> Subject:
>         Limiting rows
>    Date:
>         Mon, 13 Jul 1998 14:14:10 +0200
>    From:
>         Stefan Bleicher 
>      To:
>         akaplan@interaccess.com
> 
> Hi  Ari,
> 
> I have a question about limiting  the rows being returned by a select
> statement.
> 
> I have a large table and want to get only some rows out of this table. I
> know the rownum clause, but this is not the
> solution to my problem, because I want to get the rows (in alphabetic order)
>  for example from row 3 to row 5, not
> from row 1 to row 5.
> 
> Example:
> 
> NAME
> 
> Alber
> Albrecht
> Ari
> Bleicher
> Black
> Miller
> Percy
> Robert
> Robin
> Walter
> 
> SELECT * FROM ... WHERE ROWNUM < 6 ORDER BY NAME    => All rows > 6  =>
> Alber, Albrecht,Ari,
> Bleicher, Black
> 
> But I want to get  the result Ari, Bleicher, Black (rows 3 to 5). Do you
> know the SQL-statement to get this.
> 
> Please answer to
> 
>     sbleicher@ibl.de
> 
> Thanks for your help
> Stefan
> 
Stefan,

As far as I know this is very hard if not impossible in SQL. This is because
ROWNUM is a pseudo-column and you can never have " ROWNUM > " in the WHERE
clause. There are two ways I can think of to do this:

FIRST METHOD:
Use PL/SQL:
1) open a cursor.
2) loop through the cursor keeping track of the record number with a variable.
3) output only the data when the variable is between 3 and 5.

SECOND METHOD:
1) Create a new table:
   CREATE table_b AS
   SELECT ROWNUM AS ROW_NUMBER, column_a, column_b, ...
   FROM table_a
   ORDER BY column_a
2) Your table (table_b) has a new column called ROW_NUMBER. You can select
   your records with:
    SELECT * FROM table_b
    WHERE ROW_NUMBER > 2 AND ROW_NUMBER < 6

Best of luck!


-Ari Kaplan
Independent Oracle DBA Consultant

------------------------------------------------------------------------------------------------------
MIKE CONRAD GIVES THE FOLLOWING ADVICE:

Date: Tue, 1 Feb 2000 15:29:08 -0600
From: "Conrad, Mike" 
To: "'akaplan@interaccess.com'" 
Subject: Oracle Tip 220 - Limiting rows to the Top X meeting a condition

Here is one way to do this using only SQL and not creating another table.

The Length function is only here so that we can use the Group By.


select *
FROM  ( select  rownum row_num, A.description
                from    (SELECT  description,
                                 length(description)
                         FROM    whatever_table
                                 group by description)    A
)  B
where B.row_num > 2 and B.row_num < 6;

Thanks,
        Michael A. Conrad
        MetaSolv Software, Inc.
        5560 Tennyson Parkway
        Plano, Texas 75024
        Voice: (972) 403-8582
        Fax: (972) 403-8333
        mconrad@MetaSolv.com
        http://www.metasolv.com/
                                   

Back to Ari Kaplan's Home Page Back to Ari Kaplan's Home Page