Working of ROWNUM in Oracle

[pradeep kaltari]

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:
[code=oracle]
SELECT *
FROM ( SELECT ENAME
FROM EMP
WHERE UPPER(ENAME) LIKE 'A%'
ORDER BY ENAME ASC
)
WHERE ROWNUM=1
[/code]

Now if i want to find out the second employee in alphabetical order who's name starts with 'A'. I modified the query as:
[code=oracle]
SELECT *
FROM ( SELECT ENAME
FROM EMP
WHERE UPPER(ENAME) LIKE 'A%'
ORDER BY ENAME ASC
)
WHERE ROWNUM=2
[/code]

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

[amitpatel66]

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:

[code=oracle]
SELECT *
FROM ( SELECT ENAME,rownum rn
FROM EMP
WHERE UPPER(ENAME) LIKE 'A%'
ORDER BY ENAME ASC
)
WHERE rn = 2
[/code]

[pradeep kaltari]

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:

[code=oracle]
SELECT *
FROM ( SELECT ENAME,rownum rn
FROM EMP
WHERE UPPER(ENAME) LIKE 'A%'
ORDER BY ENAME ASC
)
WHERE rn = 2
[/code]

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

[amitpatel66]

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:

[code=oracle]
rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;
[/code]

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

[Dharmaraju]

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:

[code=oracle]
SELECT *
FROM ( SELECT ENAME,rownum rn
FROM EMP
WHERE UPPER(ENAME) LIKE 'A%'
ORDER BY ENAME ASC
)
WHERE rn = 2
[/code]

/*************** *************** *************** *************** *************** ************/

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

[pradeep kaltari]

[code=oracle]
rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;
[/code]


THE ROWNUM -- is incremented only AFTER the row is output

This is exactly what I was looking for!!

Thanks Amit.

[lalitha SS]

Thank you. Got the needed info