sql oracle duplicates

Question

Here's my ORACLE table

HAVE******
asset_no, sub,          add_dtm
11510     FOX HOLLOW    8/1/2008 8:00:01 AM
11510     FOX HOLLOW    11/1/2011 1:30:01 PM
11510     FOX HOLLOW    10/1/2012 8:00:01 AM
11511     TOWNE NORTH   6/25/2008 5:23:15 PM
11512     EAST SUB      7/23/2010 2:50:44 PM

WANT******
11510     FOX HOLLOW    10/1/2012 8:00:01 AM
11511     TOWNE NORTH   6/25/2008 5:23:15 PM
11512     EAST SUB      7/23/2010 2:50:44 PM    

i have duplicate asset_no in the table as above (11510) with different dates i need the max date for each duplicate asset_no. When i do a select asset_no, max(add_dtm) i get what i want but i need the 'sub' field too, select asset_no, sub, max(add_dtm), then thats when i get the result shown above.

Answer 1

There are several ways to do this - see SQL Fiddle with Demo of all queries

You can use a subquery:

select t1.asset_no,
  t1.sub,
  t1.add_dtm
from table1 t1
inner join
(
  select max(add_dtm) mxdate, asset_no
  from table1
  group by asset_no
) t2
  on t1.add_dtm = t2.mxdate
  and t1.asset_no = t2.asset_no

or you can use CTE using row_number():

with cte as
(
  select asset_no,
    sub,
    add_dtm,
    row_number() over(partition by asset_no 
                      order by add_dtm desc) rn
  from table1
) 
select *
from cte
where rn = 1

Or without CTE using row_number():

select *
from 
(
  select asset_no,
    sub,
    add_dtm,
    row_number() over(partition by asset_no 
                      order by add_dtm desc) rn
  from table1
) x
where rn = 1