Re: Tricky SQL Problem: Top "n" of queried records

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/22
Message-ID: <35158a75.5989169_at_www.sigov.si>#1/1


On Sun, 22 Mar 1998 14:46:01 GMT, tzadkiel_at_surfnetcity.com.au (x) wrote:

Hi, tzad

Your sollution works, but I have some remarks on some of your statings.

>Once again, ROWNUM doesn't help us here. Rownum tells us the order in
>which rows were retrieved from the database BEFORE any sort/selection
>criteria are applied.

Not quite true. You are right that ROWNUM is applied before any *explicit* sorting is done (like ORDER BY or GROUP BY), but if Oracle has to perform any *implicit* sorting, then ROWNUM is applied on the sorted resultset! I made use of this fact in my sollution, posted a few days ago (see also below).

>....[SNIP].....
>You can, however, get the result you are looking for with a little more
>complex query.
>
>select deptno, sumsal, ranking
>from (select a.deptno, a.sumsal, count(*) ranking
> from (select deptno, sum(salary) sumsal
> from emp
> group by deptno) a,
> (select deptno, sum(salary) sumsal
> from emp
> group by deptno) b
> where b.sumsal > a.sumsal
> or (b.sumsal = a.sumsal and b.deptno >= a.deptno)
> group by a.deptno, a.sumsal)
>where ranking between 1 and 3
>/

My sollution is by far more simple and it (properly) uses the ROWNUM pseudocolumn. Here it goes once again:

SELECT a.deptno, a.sal sumsal, ROWNUM ranking FROM

    (SELECT deptno, SUM(sal) sal FROM emp GROUP BY deptno) a,      dual
  WHERE -1*a.sal = DECODE(dual.dummy(+),'X',0,0)   AND ROWNUM <= 3
/

But the main difference betwen the both is the performance. In your query, for every single row in a table a full table scan is performed, while in mine only one full scan of the table is applied in addition to the full scan of a single row table DUAL! Now, on a small table like SCOTT.EMP, the difference will be insignifficant, but when I tested both on a 100.000 row table, mine finnished in less then 30 seconds, while yours would probably last forewer on my Personal Oracle (I killed it after about half an hour).

>Now, if you want to you can go buy the "101 SQL question" book that other
>guy keeps flogging and use whatever their answer is. I'm sure it's
>better than mine, since theirs is printed nice in a book and everything.
>They probably got fancy letters after their names, too.
>
>I came up with this one on my own, though, and I'm pretty proud of it
>because I think it's pretty cool. I'm just a self-taught country
>programmer, but i do ok sometimes. And i feel pretty strongly that
>information is something better spread around than kept to one's ownself.

Agree with you completely.

>tzad

Regards,


Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================
The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Mar 22 1998 - 00:00:00 CET

Original text of this message