Re: SQL - Selecting top n rows

You're right, TOP is non-standard, as is much of Microsoft's version of SQL (both for Access and SQL Server).
If you have an index on age, you can use a hint to hit the index descending: SELECT /*+ INDEX_DESC (person person_i) */ age
FROM person
WHERE ROWNUM <=n;

The hint tells the optimizer to use the proscribed index and scan it descending. The WHERE clause says to stop when you get n rows.

Hints are embedded inside comments, either single or multiline. They immediately follow the SELECT keyword. Note there is no space between the comment token and the hint indicator (the plus sign). --+ <hint>
/*+ <hint> */
Hints are covered in the Developer's Reference.

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
John Wells wrote in message <73v006$tqt$1_at_newsreader4.core.theplanet.net>...
>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?
>
>John
>John_at_well5.freeserve.co.uk
>
>
Received on Mon Nov 30 1998 - 16:47:59 CST