SQL Query With Row_Number, order by and where clause

Question

I have the following SQL query:

select
     ID, COLUMN1, COLUMN2
from
     (select ID, COLUMN1, COLUMN2, row_number() over (order by 2 DESC) NO from A_TABLE)
where
     NO between 0 and 100

What I am trying to do is to select the first 100 records of the query

select ID, COLUMN1, COLUMN2 from ATABLE order by 2 DESC

And here are the problems:

1. Apparently, the order by clause is not working. I've noticed that I have to add another order by 2 DESC clause, just after (...) from ATABLE, for my query to work. Is there something I do wrong? Or is it expected behaviour?

2. How can I add a where clause? Let's say I need to select only the first 100 records of the table where COLUMN1 like '%value%'. I've tried adding the where clause after (...) from ATABLE but it produced an error...

Help? Thanks.

PS: I'm using Oracle 10g R2.

Forestaysail

Can you actually include the other queries that you've tried instead of just explaining them? It might make it easier to spot the problem. – Icelander

Answer 1

rownum is a pseudo column that counts rows in the result set after the where clause has been applied.

Is this what you're trying to get?

SELECT *
FROM ( 
    SELECT id, column1, column2
    FROM atable ORDER BY 2 DESC
) 
WHERE ROWNUM < 100;

Because it's a pseudo column that is strictly a counter of rows resulting from the where clause it will not allow you to do pagination (i.e. between 200 & 300).

This is probably what you're looking for:

SELECT *
FROM
 (SELECT a.*, rownum rnum FROM
     (SELECT id, column1, column2 FROM atable ORDER BY 2 DESC) a WHERE rownum <= 300)
WHERE rnum >= 200;

Libratory

And me too. :-) – Legit

Answer 2

Check out this Oracle FAQ. In particular this part:

  SELECT * 
  FROM (SELECT a.*, rownum RN 
      FROM (SELECT * 
                         FROM t1 ORDER BY key_column) a
         WHERE rownum <=7)
 WHERE rn >=5

Gargan

For those that are interested, this is called a TOP-N query – Novanovaculite

I know about top-n but right now it is not helping me at all, considering that I have to fetch exactly n records between value1 and value2. – Forestaysail

I thought that is what my query did :) – Gargan

Answer 3

To answer your first question: Don't use a column number in your order by clause, but use the column name. I don't fully understand your second question, because adding a WHERE in your most inner SELECT should do the trick:

select ID
,      COLUMN1
,      COLUMN2
from  (select ID
       ,      COLUMN1
       ,      COLUMN2
       ,      row_number() over (order by COLUMN1 DESC) NO
       from   A_TABLE
       where  COLUMNX LIKE '%SOME VALUE%'
      )
where  NO between 0 and 100

P.S. (to willcodejavaforfood) I think using row_number() is better when you want the rows to be ordered. It saves an inner view (big win for readability).

Photobathic

+1 for the ROW_NUMBER() usage which is easier to understand. -1 for your P.S. suggesting the ORDER BY in an inner view won't work - willcodejavaforfood's suggestion works perfectly well. – Spies

Edited my P.S. (removed the part about using an order by in a sub-query) because i have no prove and it will be hard to construct (if possible...) – Photobathic

Answer 4

Why don't you use

Select top 100 ID, Column1, Column2
From A_Table
where Column1 like '%value%'
order by Column2 desc

Clapperclaw

What if I need to select the rows between row_num 200 and 300 ? This is why I tried to use row_num in the first place... – Forestaysail

Sorry, I didn't see you were using Oracle. – Clapperclaw

Answer 5

Here you will get the limited record from the oracle database without the usage of rownum

select * from 
    ( select ,column1,column2,row_number() over (order by columnName) as rnum 
      from table_name) 
where rnum between 5 and 10;

Axel