Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tricky SQL Problem: Top "n" of queried records
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 CST