Hello Tom,

I hope all is well.

The database is 11.2.0.2 Enterprise Edition 64Bit.

I know the subject text is not entirely about what I am trying to achive here, but I did not know how to word it without actually asking the question.

I have a table:

create table tbl_history (object_id int, seq int, emp_id int );

with this data in it:

insert into tbl_history values (100, 9, 625);
insert into tbl_history values (100, 7, 625);
insert into tbl_history values (100, 6, 33);
insert into tbl_history values (100, 5, 33);
insert into tbl_history values (100, 4, 241);
insert into tbl_history values (100, 3, 125);

insert into tbl_history values (200, 5, 625);
insert into tbl_history values (200, 4, 33);
insert into tbl_history values (200, 3, 33);
insert into tbl_history values (200, 2, 241);
insert into tbl_history values (200, 1, 125);

The query needs to return only "2 records per object_id" and those two records should be sorted by "seq" column in Descending order and the top 2 DISTINCT "emp_id"s will be listed. Based on the sample data above, it should look like:

object_id seq emp_id
--------- --- ------
100 9 625
100 6 33
200 5 625
200 4 33

Please let me know if the question makes sense and wether this could be achieved in a single SQL statement without writing a PL/SQL function.

Thank you,
Sinan

ops$tkyte%ORA11GR2> select *
  2    from (
  3  select object_id, max(seq) mseq, emp_id, dense_rank() over (partition by object_id order by max(seq) DESC ) dr
  4    from tbl_history
  5   group by object_id, emp_id
  6         )
  7   where dr <= 2
  8   order by object_id, mseq desc
  9  /

 OBJECT_ID       MSEQ     EMP_ID         DR
---------- ---------- ---------- ----------
       100          9        625          1
       100          6         33          2
       200          5        625          1
       200          4         33          2