Given a database table where multiple rows have the same values and only the most recent record is to be returned
Posted
by Jim Lahman
on Geeks with Blogs
See other posts from Geeks with Blogs
or by Jim Lahman
Published on Mon, 03 Jan 2011 21:09:25 GMT
Indexed on
2011/01/03
21:54 UTC
Read the original article
Hit count: 324
I have a table where there are multiple records with the same value but varying creation dates. A sample of the database columns is shown here:
1: select lot_num, to_char(creation_dts,'DD-MON-YYYY HH24:MI:SS') as creation_date
2: from coil_setup
3: order by lot_num
LOT_NUM CREATION_DATE
------------------------------ --------------------
1435718.002 24-NOV-2010 11:45:54
1440026.002 17-NOV-2010 06:50:16
1440026.002 08-NOV-2010 23:28:24
1526564.002 01-DEC-2010 13:14:04
1526564.002 08-NOV-2010 22:39:01
1526564.002 01-NOV-2010 17:04:30
1605920.003 29-DEC-2010 10:01:24
1945352.003 14-DEC-2010 01:50:37
1945352.003 09-DEC-2010 04:44:22
1952718.002 25-OCT-2010 09:33:19
1953866.002 20-OCT-2010 18:38:31
1953866.002 18-OCT-2010 16:15:25
Notice that there are multiple instances of of the same lot number as shown in bold.
To only return the most recent instance, issue this SQL statement:
1: select lot_num, to_char(creation_date,'DD-MON-YYYY HH24:MI:SS') as creation_date
2: from
3: (4: select rownum r, lot_num, max(creation_dts) as creation_date
5: from coil_setup group by rownum, lot_num
6: order by lot_num
7: )8: where r < 100
LOT_NUM CREATION_DATE
------------------------------ --------------------
2019416.002 01-JUL-2010 00:01:24
2022336.003 06-OCT-2010 15:25:01
2067230.002 01-JUL-2010 00:36:48
2093114.003 02-JUL-2010 20:10:51
2093982.002 02-JUL-2010 14:46:11
2093984.002 02-JUL-2010 14:43:18
2094466.003 02-JUL-2010 20:04:48
2101074.003 11-JUL-2010 09:02:16
2103746.002 02-JUL-2010 15:07:48
2103758.003 11-JUL-2010 09:02:13
2104636.002 02-JUL-2010 15:11:25
2106688.003 02-JUL-2010 13:55:27
2106882.003 02-JUL-2010 13:48:47
2107258.002 02-JUL-2010 12:59:48
2109372.003 02-JUL-2010 20:49:12
2110182.003 02-JUL-2010 19:59:19
2110184.003 02-JUL-2010 20:01:03
© Geeks with Blogs or respective owner
