Re: SQL: Top-N with a twist?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 9 Jul 2001 19:28:55 -0700
Message-ID: <9idp9701rbm@drn.newsguy.com>

In article <77e87b58.0107091257.7abb5d00_at_posting.google.com>, onlyforposting_at_yahoo.com says...
>
>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...

Oracle8i release 2 and up with analytic functions can do this readily. We'll use inline views to generate the set of partno/empno/cnts, use the row_number() analytic function to assign a rownum by CNT desc within each partno and then just pick off "row 1" from each partition:

ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t select mod(rownum,5), empno from (select * from emp order by ename);

14 rows created.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t select mod(rownum,5), empno from (select * from emp order by hiredate);

14 rows created.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t select mod(rownum,5), empno from (select * from emp order by job);

14 rows created.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t select mod(rownum,5), empno from (select * from emp order by deptno);

14 rows created.

ops$tkyte_at_ORA817.US.ORACLE.COM> insert into t select mod(rownum,5), empno from (select * from emp order by sal);

14 rows created.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> select partno, empno, cnt   2 from ( select partno, empno, cnt,

3                  row_number() over ( partition by partno order by cnt desc ) r
  4             from ( select partno, empno, count(*) cnt
  5                      from t
  6                     group by partno, empno
  7                  )
  8         )

  9 where r = 1
 10 /

    PARTNO EMPNO CNT
---------- ---------- ----------

         0       7698          3
         1       7369          2
         2       7499          2
         3       7844          3
         4       7521          4

ops$tkyte_at_ORA817.US.ORACLE.COM>

Again, this needs Oracle8i release 2 (816) and up for the analytic function

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Mon Jul 09 2001 - 21:28:55 CDT