Re: SQL - Selecting top n rows

John Wells schrieb:
>
> Help! I've got limited SQL knowledge, and need to get the top n rows of a
> query. In MS Access (bleargh!) it's easy:
>
> SELECT TOP n name, age
> FROM person
> ORDER BY age DESC;
>
> But from what I can see, Oracle has no 'TOP' (non-standard?).
>

So, is there an easy way to do this on Oracle?

Yes, take a look at Jurij Modic's solutions: (BTW, this question arrises often. Just take a look at dejanews news archive next time.)

Couple of possible ways to do it...

1. (Caution - might be extremely slow on large tables) SELECT * FROM emp e1 WHERE 1000 >= (SELECT COUNT(sal) FROM emp e2 WHERE e2.sal >= e1.sal) ORDER BY e1.sal DESC;
2. (Much faster, for Oracle7.2 and higher) SELECT emp.* FROM emp, (SELECT rowid x, -1*sal FROM emp GROUP BY -1*sal, rowid) e2 WHERE emp.rowid = e2.x AND rownum <= 1000 ORDER BY emp.sal DESC;
3. (The fastest of all three) SELECT /*+ RULE */ emp.* FROM emp, dual WHERE -1*emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL) AND rownum <= 1000 ORDER BY emp.sal DESC;

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Tue Dec 01 1998 - 02:57:04 CST