Asked  7 Months ago    Answers:  5   Viewed   31 times

So I want to select a range of rows in an Oracle DB. I need to do this because I have millions of rows in the table and I want to paginate results to the user (if you know another way to do this on the client side, I'm using JavaFX if it matters but I don't think it's a good idea to send all the datas over the network to paginate them on the client side).

So after reading this post: SQL ROWNUM how to return rows between a specific range, I have the following query:

Select * From (Select t.*, rownum r from PERSON t) Where r > 100 and r < 110;

The 100 and 110 are just example. In the application, I just ask for the lower bound and add a size of 10_000 to fetch the next 10_000 rows.

Now the rownum column appears in the result and I don't want to see it. As I'm not very experienced with SQL, here's my questions:

  1. Why (this was my first attempt until I search on SO) Select * From Person Where rownum > 100 and rownum < 110; returns 0 rows ?

  2. Why there is no simple way to do something like Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?

  3. How to get rid of the r column in the resulting values? From there SQL exclude a column using SELECT * [except columnA] FROM tableA? I need apparently to create a view or a temporary table, but is there another way considering my query?

  4. Does it ensure correct pagination? I read this article section "Pagination with ROWNUM", that said I should order the values by something unique to get a consistent pagination (so I guess ordering by rownum is fine, if you can confirm). Doesn't it defeat the purpose of using FIRST_ROWS(N)?

I hope it's not too much, I could split into separate questions, but I think it's relevant to have them collapsed as they are closely related.

 Answers

50

You have 4 questions, and all revolve around the usage and functionality of ROWNUM. I will answer each question one-by-one.

Why (this was my first attempt until I search on SO) Select * From Person Where rownum > 100 and rownum < 110; returns 0 rows ?

Nice explanation by Thomas Kyte regarding ROWNUM and pagination here.

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1.

Why there is no simple way to do something like Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound ?

Yes, there is. From Oracle 12c onwards, you could use the new Top-n Row limiting feature. See my answer here.

For example, the below query would return the employees between 4th highest till 7th highest salaries in ascending order:

SQL> SELECT empno, sal
  2  FROM   emp
  3  ORDER BY sal
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

     EMPNO        SAL
---------- ----------
      7654       1250
      7934       1300
      7844       1500
      7499       1600

SQL>

How to get rid of the r column in the resulting values?

Instead of select *, list the required column names in the outer query. For frequently using the query, creating a view is a simple one time activity.

Alternatively, in SQL*Plus you could use the NOPRINT command. It will not display the column name you don't want to display. However, it would only work in SQL*Plus.

For example,

COLUMN column_name NOPRINT

For example,

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> COLUMN dname NOPRINT
SQL> COLUMN LOC NOPRINT
SQL> SELECT * FROM dept;

    DEPTNO
----------
        10
        20
        30
        40

SQL>

Does it ensure correct pagination?

Yes, if you write the pagination query correctly.

For example,

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   t
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

Or, use the new row limiting feature on 12c as I have shown above.

Few good examples here.

Tuesday, June 1, 2021
 
Zach
answered 7 Months ago
35

This should work (works for me)

update table_a outer 
set sequence_column = (
    select rnum from (

           -- evaluate row_number() for all rows ordered by your columns
           -- BEFORE updating those values into table_a
           select id, row_number() over (order by column1, column2) rnum  
           from table_a) inner 

    -- join on the primary key to be sure you'll only get one value
    -- for rnum
    where inner.id = outer.id);

OR you use the MERGE statement. Something like this.

merge into table_a u
using (
  select id, row_number() over (order by column1, column2) rnum 
  from table_a
) s
on (u.id = s.id)
when matched then update set u.sequence_column = s.rnum
Thursday, June 24, 2021
 
Hilmi
answered 6 Months ago
31

you cant have a view with 1000+ columns, so cheat a little.

select *
  from foo f, foo2 f2
 where (f.rowid, f2.rowid) in (select r, r2
                                 from (select r, r2, rownum rn
                                         from (select /*+ first_rows */ f.rowid r, f2.rowid r2
                                                 from foo f, foo2 f2
                                                where f.c1 = f2.a1 
                                                  and f.c2 = '1'
                                                order by f.c1))
                                where rn >= AAA
                                  and rownum <= BBB)


order by whatever;

now put any where clauses in the innermost bit (eg i put f.c1 = '1').

BBB = pagesize. AAA = start point

Thursday, August 19, 2021
 
Blacksonic
answered 4 Months ago
81
->where('name_item LIKE :substr', array(':substr' => '%name%'))

Just put it in the variables part?

Wednesday, November 24, 2021
 
Yishu Fang
answered 1 Week ago
99

Please use the thread below. Pretty good explanations. https://community.oracle.com/thread/210143 Here are two working solutions.

  1. Use the row_number function for 8i and 9i :
    ROW_NUMBER() OVER (ORDER BY ASC) AS ROW_NUMBER.
  2. Reuse Rownum pseudo column (This seems to be better) SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM ( SELECT * FROM mytable ORDER BY paginator, id ) t ) WHERE rn BETWEEN 1 AND 3
Monday, November 29, 2021
 
Navaneeth K N
answered 3 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share