rownum issue in oracle query
11,429
Solution 1
Please use the thread below. Pretty good explanations. https://community.oracle.com/thread/210143 Here are two working solutions.
- Use the row_number function for 8i and 9i :
ROW_NUMBER() OVER (ORDER BY ASC) AS ROW_NUMBER. - 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
Solution 2
You need to apply ROWNUM
after Oracle figures out which rows to return. The only reliable way is this:
SELECT * FROM (
Select distinct a.id
from table1 a, table b
where ( a.id= b.id or a.id = b.secondid )
) WHERE ROWNUM < 200;
Comments
-
Neeraj almost 2 years
I m trying to run this query but it returns zero rows. Any clues why?
Select distinct a.id from table1 a, table b where ( a.id= b.id or a.id = b.secondid ) and rownum < 200;
But if I run the above query without the ROWNUM clause it finds records:
Select distinct a.id from table1 a, table b where ( a.id= b.id or a.id = b.secondid );
I'm confused why the first query is not working.
-
Gordon Linoff about 11 years. . saying "it is not working" is not helpful. You should say what the problem is.
-
Neeraj about 11 yearsnot working means not getting the correct output. It returned Zero rows if i add rownum in the end.
-
-
Neeraj about 11 yearsyes thanks, i figured that out but i was thinking it should have worked before as well. like if you remove the or clause then it works.e.g. Select distinct a.id from table1 a, table b where a.id= b.id and rownum < 200;
-
Ed Gibbs about 11 years@Neeraj - there are some cases where
ROWNUM
will work without resorting to an outer query, but they're rare. There's a pretty good explanation on the Oracle "Ask Tom" site here.