Re: ROWNUM between 5 and 30 doesn't work

From: Eltschinger Markus <do_not_email_at_home.com>
Date: Sun, 8 Jun 2003 12:34:34 +0200
Message-ID: <1055068476.515771_at_exnews>

hello Sharron,

the use of the analytic function row_number() over() in Oracle9i is one of many solutions to your problem:

SELECT ROW_NUM,
       USERNAME
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY USERNAME) AS ROW_NUM,

               USERNAME
          FROM DBA_USERS
         WHERE USERNAME NOT IN ('SYS','SYSTEM'))

 WHERE ROW_NUM BETWEEN 5 AND 30; The standard ANSI SQL clause "LIMIT TO n ROWS" isn't supported by Oracle RDBMS yet.

Kind regards,
Markus Eltschinger

---

Swisscom IT Services Ltd
Data Warehouse Development
1752 Villars-Sur-Glâne FR
Switzerland
http://www.swisscom.com/it/content/index_EN.html

"Sharron" <sharron_allen_at_raytheon.com> wrote in message news:3EE118FC.1070908_at_raytheon.com...
> Hi, I can't seem to figure out how to get oracle to show rows other than
> rows starting from 0 or 1. EX
>
> SELECT * FROM users WHERE username != 'admin' ORDER BY name LIMIT 16, 20
>
> //My sql statment that says start at row 16 and show 20 rows from there
>
> How can I do this in ORACLE.
>
> I've tried using ROWNUM but it only let's me start w/ row 0 or 1 never
> 2, 16, etc.
>
> This won't work
>
> SELECT * FROM users WHERE rownum < 22 and rownum > 3 and username != 'admin'
>
> Please help.
>
> Thanks
> Sharron
>
Received on Sun Jun 08 2003 - 12:34:34 CEST