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

From: John Griffiths <jgriffiths_at_dial.pipex.com>
Date: 1996/08/18
Message-ID: <321735D4.4BC2_at_dial.pipex.com>#1/1


Paul Young wrote:
>
> Dave has a catalogue of Stupid Pet Tricks tapes. Each has a NAME and
> an outrageous SCORE factor (higher is better and there's no limit).
> Now Dave has hundreds of these entries, say in an Oracle table.
> On his next greatest hits of David Letterman show, he wants to show
> only the best 10.
>
> Help Dave find a solution and code the SQL query.
>
> But seriously, how can you code a SQL query to first order records
> greatest to least then pick out only a pre-selected number of them
> without prior knowledge of their distribution. This strikes me as
> a common, even simple thing to do, but I can't see how to do it, and
> I've written some pretty twisted queries.
>
> Your assistance with this is appreciated. Thank you.
>
> PGY (Opinions expressed are mine and mine alone, sorry Dave)
> ################################################################
> # Paul Young #
> # youngpa_at_statcan.ca #
> # Statistics Canada #
> ################################################################

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.

Regards
John Griffiths Received on Sun Aug 18 1996 - 00:00:00 CEST

Original text of this message