Given a database table where multiple rows have the same values and only the most recent record is to be returned
- by Jim Lahman
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