Re: [Q] Daves's top 10 SQL question

In article <321735D4.4BC2_at_dial.pipex.com>, jgriffiths_at_dial.pipex.com wrote:

> SELECT
> NAME
> ,SCORE
> FROM
> StupidPetTricks
> WHERE
> ROWNUM < 11
> ORDER BY
> SCORE DESC;
>
>
> Order by clause sorts them in to reverse order.
>
> ROWNUM pseudocolumn is the number of each row returned so the ROWNUM <
> 11 indicates that only the first 10 rows should be returned.
>
> NOTE:
> Not neccessarily completely correct as it won't handle the joint 10th
> place problem, but otherwise quick and simple.

Of course, it isn't stated *how* we want to handle the joint 10th place problem. Take 'em all, even if return more than 10 rows? Take some of the "ties for 10th," but not all, so that there are exactly 10? Or some other thing? Without knowing that, perhaps the generic solution *is* best left undetermined.

Also, you may want to see the "Quota Queries" series of articles by C.J. Date in the July to September '96 _Database Programming & Design_. This type of query is given extensive examination, though its treatment may be a bit too advanced and theoretical for some.

-- 
Tim Irvin, HP-UX System Administrator / Oracle DBA
Lockheed Martin Missiles and Space, Sunnyvale, California
e-mail: irvin_at_lmsc.lockheed.com    voice:  (408) 742-0440
************ all standard disclaimers apply ************