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

From: John Griffiths <jgriffiths_at_dial.pipex.com>
Date: 1996/08/18
Message-ID: <3217576E.1034_at_dial.pipex.com>#1/1

Eric Amick wrote:
>
> John Griffiths (jgriffiths_at_dial.pipex.com) wrote:
> >Paul Young wrote:
> >> 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.
> >
> >SELECT
> > NAME
> > ,SCORE
> >FROM
> > StupidPetTricks
> >WHERE
> > ROWNUM < 11
> >ORDER BY
> > SCORE DESC;
>
> Did you actually try this? Rownums are assigned *before* the data is
> sorted, so all you did was sort the first 10 rows selected from the table.
>
> --
> Eric Amick eamick_at_clark.net
> Columbia, MD Public key available via finger

Yes. I did try it.
Yes. It did work.

However I've just checked the Oracle Manuals and you are right.

"Oracle7 assigns a ROWNUM value to each row as it is retrieved, before"
"rows are sorted for an ORDER BY clause, so an ORDER BY clause normally"
"does not affect the ROWNUM of each row. However, if an ORDER BY clause"
"causes Oracle7 to use an index to access the data, Oracle7 may

retrieve"
"the rows in a different order than without the index, so the ROWNUMs may"
"differ than without the ORDER BY clause."

When I tested it I was using a column that was also the primary index.

Thanks for the correction.
John Griffiths Received on Sun Aug 18 1996 - 00:00:00 CEST