Re: TOP N Query

Hi Billy,

   I tried the following query
> SELECT
> id
> FROM
> ( SELECT rownum SEQ, id FROM emp ORDER BY surname)
> WHERE seq BETWEEN 4 AND 11

And it worked, it gave the rows correctly from 4 to 11 even after the order by clause. I think it must be first sorting the records in the db temp space (somewhere) and then fetching it. And while fetching it assigning row nums.

--Harpreet

--
"Science is organized knowledge. Wisdom is organized life."
"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
news:at72e6$rcb$1_at_ctb-nnrp2.saix.net...


> Harpreet Singh wrote:

>

> > SQL Server Query

> > --------------------

> > SELECT id FROM (SELECT TOP 50 id FROM  (SELECT TOP 150 id FROM emp ORDER

> > BY id ASC) y ORDER BY id DESC) x

> >

> > Oracle Query

> > ---------------

> > SELECT * FROM (SELECT id FROM (SELECT * FROM (SELECT  id FROM (SELECT *

> > FROM

> > (SELECT  id FROM emp ORDER BY id ASC ) WHERE ROWNUM <= 150 ) "y" ORDER

BY


> > id DESC ) WHERE ROWNUM <= 50 ) "x")WHERE ROWNUM <= 15;

>

>

> Hmmm... this _will_ work:

> SELECT

>   id

> FROM

> ( SELECT rownum SEQ, id FROM emp )

> WHERE seq BETWEEN 41 AND 50

>

> What does not work is when you start ordering the data. Row number

reflects


> the row's number _before_ sorting. Not after. Thus, the following will not

> work:

> SELECT

>   id

> FROM

> ( SELECT rownum SEQ, id FROM emp ORDER BY surname)

> WHERE seq BETWEEN 41 AND 50

>

>

> You therefore need to add the row number after the sort. I.e.

> SELECT

>   id

> FROM

>




>   SELECT

>     rownum SEQ,

>     id

>   FROM (SELECT id FROM emp ORDER BY surname)

> )

> WHERE seq BETWEEN 41 AND 50

>

> I will not trust SQL conversion tools to provide the best or proper SQL

> conversions between databases.

>

> --

> Billy

>