Finding the last duplicate row in (Oracle) SQL

Question

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 last (non-ID-based) line of the PER Work ID. Please note that an object identifier can have several work identifiers, and we need the last work identifier for EACH.

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?

Answer 1

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

Answer 2

 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)