Re: Limit a result list

From: Joerg Banholzer <oracle_at_banholzer.de>
Date: Wed, 4 Dec 2002 10:47:16 +0000 (UTC)
Message-ID: <65f1aa4f6128447822b79f1f07ac5016.116041@mygate.mailgate.org>

"NinjaToy" <LCIYQBBEQVFA_at_spammotel.com> wrote in message news:3DEDD12E.59842C69_at_spammotel.com

> the rownum is assigned before the order by
>

Hm, so I did an extra SELECT-Statement around, but the two Statements still have not the same result (the rownum is separated from the order in this statements, isn't it?):

Statement (1):

---

SELECT *
FROM (SELECT

        A, B
      FROM 
        MyTable
      WHERE 
        Something
      ORDER BY A DESC)

WHERE RowNum < 21
MINUS
SELECT *
FROM (SELECT

        A, B
      FROM 
        MyTable
      WHERE 
        Something
      ORDER BY A DESC)

WHERE RowNum < 11
;

Statement (2):

---

SELECT *
FROM
  (SELECT A, B, ROWNUM rn
  FROM (SELECT

          A, B
        FROM 
          MyTable
        WHERE 
          Something
        ORDER BY A DESC)

  )
WHERE rn between 11 AND 20
;

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Wed Dec 04 2002 - 04:47:16 CST