Re: TOP N Query

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