SQL: Top-N with a twist?

Need help with the following:

One table with (partno,empno) of a part number and the employee who sold that part number. One emp can sell many parts and one part can be sold by many emps.

How do I get the partno and the employee who sold the largest number of those parts?

select partno,empno,count(*) from table
order by 3 desc

My final desired output is the first row in the above query for each (partno,empno). In other words, the row with the maximum count(*) for each (partno,empno)

Something like

select partno,min(empno) from
 (select partno,empno,count(*) emp_count from table) where emp_count=(select max(emp_count) from the above inline query) group by partno

What is the most efficient way of doing this? Use temporary tables to store intermediate results? Would Oracle 8i's new 'order by' in inline view feature help here?

Thanks... Received on Mon Jul 09 2001 - 15:57:50 CDT