0
select emp.*, rownum from emp where rownum=1;

o/p:

Name ID Salary Jobid Rowid
ABCD 101 12000 10    1

While giving more than one 1 in rownum column, didn't get output. Can some one please explain what is the logic behind that?

select emp.*, rownum from emp where rownum=2;

O/p:

No data found

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Bala S
  • 445
  • 1
  • 5
  • 15
  • 2
    "*Can someone explain*" - [the manual can and does explain that:](https://docs.oracle.com/database/121/SQLRF/pseudocolumns009.htm#SQLRF00255): "*Conditions testing for ROWNUM values greater than a positive integer are always false*" – a_horse_with_no_name Sep 29 '15 at 07:46
  • It doesn't work like that, you'll have to specify `where rownum<=2`. – Radu Gheorghiu Sep 29 '15 at 07:46
  • Thanks for the info. Please find below two query. 1) select emp.*, rownum from emp where rownum=2; -- No result. 2) select * from (select emp.*, rownum rm from emp) where rm=2; -- One row selected. Can you please explain the difference? – Bala S Sep 29 '15 at 07:51
  • It has had been asked before and is a FAQ. 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;` Please see the topic marked as duplicate. – Lalit Kumar B Sep 29 '15 at 07:57
  • @BalakumarSubramanian "*select * from (select emp.*, rownum rm from emp) where rm=2; -- One row selected. Can you please explain the difference?*" That's because ROWNUM is a pseudo column, thus in your 1st query the pseudo column is not yet incremented to two. However, in your 2nd query, the subquery returns the result set and therefore it acts as a static column and you are able to fetch the rows. – Lalit Kumar B Sep 29 '15 at 08:01

0 Answers0