CODEWITHSUNDEEP
sqloracle-databasejoinself
Fonzy
Fonzy

Reputation: 201

sql oracle duplicates

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.

Upvotes: 1

Views: 170

Answers (3)

rs.
rs.

Reputation: 27467

Try this

WITH CTE AS
(
SELECT asset_no, sub,add_dtm, 
row_number() over (partition by asset_no, sub order by add_dttm desc) 
rn from table1
)

select * from cte where rn = 1;

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Probably the simplest way is to use an analytic (aka. window) function, like this:

SELECT *
FROM (
    SELECT 
        YOUR_TABLE.*, 
        RANK() OVER (PARTITION BY asset_no ORDER BY add_dtm DESC) R
    FROM YOUR_TABLE
)
WHERE R = 1

Note that this will return multiple rows in case you have multiple equal maximal dates (for the same asset_no). To pick just one of them (at random), you could use ROW_NUMBER() OVER ....

Or, do it the old-fashion way:

SELECT *
FROM YOUR_TABLE T1
WHERE
    add_dtm = (
        SELECT MAX(add_dtm)
        FROM YOUR_TABLE T2
        WHERE T1.asset_no = T2.asset_no
    );

This would work even on a DBMS that doesn't support analytic functions (unlike Oracle).

[SQL Fiddle]

Upvotes: 2

Taryn
Taryn

Reputation: 247850

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

Upvotes: 3

Related Questions