CODEWITHSUNDEEP
sqloracle-databaseselect
Randyaa
Randyaa

Reputation: 2945

Finding the last duplicate row in (Oracle) SQL

We have a history table which is defined as follows:

--ID (pk) -----------Object ID--------------Work ID--------date------
  1                  1111                   AAAA           1/1/2010
  2                  1111                   AAAA           1/2/2010
  3                  2222                   BBBB           1/1/2010
  4                  3333                   CCCC           1/1/2010
  5                  1111                   DDDD           1/3/2010

We need the latest (date-based NOT id-based) row PER Work ID. Note that an object ID can have multiple work id's and we need the latest for EACH work ID.

What we need as our result set:

ID (pk) -----------Object ID--------------Work ID--------date------
2                  1111                   AAAA           1/2/2010
3                  2222                   BBBB           1/1/2010
4                  3333                   CCCC           1/1/2010
5                  1111                   DDDD           1/3/2010

Thoughts/Ideas?

Upvotes: 4

Views: 5292

Answers (2)

Quassnoi
Quassnoi

Reputation: 425683

SELECT  *
FROM    (
        SELECT  h.*, 
                ROW_NUMBER() OVER (PARTITION BY workID ORDER BY date DESC) AS rn
        FROM    history
        )
WHERE   rn = 1

Upvotes: 5

Falcon
Falcon

Reputation: 3170

select * from your_table a where (a.date, a.work_id)  in (select max(b.date), b.work_id from your_table b where a.work_id=b.work_id group by work_id)

Upvotes: 0

Related Questions