Geeks With Blogs
Jim Lahman's Blog Fortitude|Endurance|Faith|Teamwork

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

Posted on Monday, January 3, 2011 4:09 PM | Back to top


Comments on this post: Given a database table where multiple rows have the same values and only the most recent record is to be returned

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Jim Lahman | Powered by: GeeksWithBlogs.net