By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,812 Members | 1,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,812 IT Pros & Developers. It's quick & easy.

Working of ROWNUM in Oracle

Expert 100+
P: 102
Hi All,
I was just going through EMP table in oracle Scott schema.

Say, I want to find out the first employee in alphabetical order who's name starts with 'A'. the following query gives me the desired result:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (  SELECT ENAME 
  3.         FROM EMP
  4.         WHERE UPPER(ENAME) LIKE 'A%'
  5.         ORDER BY ENAME ASC
  6.         )
  7. WHERE ROWNUM=1
  8.  
Now if i want to find out the second employee in alphabetical order who's name starts with 'A'. I modified the query as:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (  SELECT ENAME 
  3.         FROM EMP
  4.         WHERE UPPER(ENAME) LIKE 'A%'
  5.         ORDER BY ENAME ASC
  6.         )
  7. WHERE ROWNUM=2
  8.  
But this does not give me any rows although there are many employees whos names start with 'A'.

Please let me know what could be the problem in this case.

Thanks,
Pradeep
Nov 21 '07 #1
Share this Question
Share on Google+
6 Replies


amitpatel66
Expert 100+
P: 2,367
Hi Pradeep,

ROWNUM are assigned every time when the query is executed and it starts with 1.

You cannot work the way you tried in second query to get second employee.

Try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (  SELECT ENAME,rownum rn
  3.         FROM EMP
  4.         WHERE UPPER(ENAME) LIKE 'A%'
  5.         ORDER BY ENAME ASC
  6.         )
  7. WHERE rn = 2
  8.  
Nov 21 '07 #2

Expert 100+
P: 102
Hi Pradeep,

ROWNUM are assigned every time when the query is executed and it starts with 1.

You cannot work the way you tried in second query to get second employee.

Try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (  SELECT ENAME,rownum rn
  3.         FROM EMP
  4.         WHERE UPPER(ENAME) LIKE 'A%'
  5.         ORDER BY ENAME ASC
  6.         )
  7. WHERE rn = 2
  8.  
Hi amit,
Thanks for your reply.

I agree that the query you posted would give the results, I also know there are other methods to get the same result.

What I wanted to know is how exactly ROWNUM works. If i can say ROWNUM=1, then why doesn't ROWNUM=2 work??

- Pradeep
Nov 21 '07 #3

amitpatel66
Expert 100+
P: 2,367
Hi amit,
Thanks for your reply.

I agree that the query you posted would give the results, I also know there are other methods to get the same result.

What I wanted to know is how exactly ROWNUM works. If i can say ROWNUM=1, then why doesn't ROWNUM=2 work??

- Pradeep
Pradeep,

rownum is assigned to rows AS THEY SATISFY the predicate.

the logic would be:

Expand|Select|Wrap|Line Numbers
  1.    rownum = 1
  2.    for x in ( select * from A )
  3.    loop
  4.        if ( x satisifies the predicate ) 
  5.        then
  6.              OUTPUT the row
  7.              rownum = rownum + 1
  8.        end if;
  9.    end loop;
  10.  
in the case of where rownum = 1, the first row passes the test, is output and rownum goes
to 2. No other row ever satisfies the predicate and rownum stays at 2 for the rest of
the query.

in the case of where rownum = 2, the first row is rownum 1, it fails. The next row is
ALSO rownum = 1 and likewise fails. And so on. There can be NO row 2 if there is not a
row 1.

THE ROWNUM -- is incremented only AFTER the row is output
Nov 21 '07 #4

P: 13
Hi Pradeep,

ROWNUM are assigned every time when the query is executed and it starts with 1.

You cannot work the way you tried in second query to get second employee.

Try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (  SELECT ENAME,rownum rn
  3.         FROM EMP
  4.         WHERE UPPER(ENAME) LIKE 'A%'
  5.         ORDER BY ENAME ASC
  6.         )
  7. WHERE rn = 2
  8.  
/************************************************** *************************************/

HI IN ORACLE 8i WE CANT ABLE TO USE = OPERATOR FOR ROWNUM SO U CAN TRY USING <,<=

SELECT *
FROM ( SELECT ENAME,rownum rn
FROM EMP
WHERE UPPER(ENAME) LIKE 'A%'
ORDER BY ENAME ASC
)
WHERE rn <= 2
Dec 3 '07 #5

Expert 100+
P: 102

Expand|Select|Wrap|Line Numbers
  1.    rownum = 1
  2.    for x in ( select * from A )
  3.    loop
  4.        if ( x satisifies the predicate ) 
  5.        then
  6.              OUTPUT the row
  7.              rownum = rownum + 1
  8.        end if;
  9.    end loop;
  10.  

THE ROWNUM -- is incremented only AFTER the row is output
This is exactly what I was looking for!!

Thanks Amit.
Dec 4 '07 #6

P: n/a
Thank you. Got the needed info
Oct 7 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.