Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: TOP N Query

Re: TOP N Query

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 11 Dec 2002 11:59:36 +0200
Message-ID: <at72e6$rcb$1@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
Received on Wed Dec 11 2002 - 03:59:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US