rownum issue in oracle query

2019-06-05 13:56发布

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.

3条回答
地球回转人心会变
2楼-- · 2019-06-05 14:37

Change "and rownum < 200" to "WHERE rownum < 200"

查看更多
何必那么认真
3楼-- · 2019-06-05 14:46

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
查看更多
Root(大扎)
4楼-- · 2019-06-05 14:51

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;
查看更多
登录 后发表回答