Help Need for a Query

[Prabu Krishnan]

Hi
Please Help me that I faced this Question in an interview but I cant
Get The Proper Answer from Any Where..

SQl> Select * from emp where rownum < 10;

Returns 9 Records BUT,

SQl> Select * from emp where rownum > 5;

Returns 0 Records

Why ? what is the Reason..

Please Help me.

[Vijai Kumar]

Hi,

rownum is a pseudo column.

When you say select * from emp where rownum < 10; it will select only
the first 9 rows from the total query output.
Each and every row returned will have the same rownum =1;

When you say select * from emp where rownum >1; it will not return any
rows beacuse every row has got rownum value = 1.

Try rownum=1 in the above query u will notice that all rows will be
returned.

There is also rowid feature try it out.

cheers
vijai

[Bent Stigsen]

Vijai Kumar wrote:

Hi,

rownum is a pseudo column.

When you say select * from emp where rownum < 10; it will select only
the first 9 rows from the total query output.
Each and every row returned will have the same rownum =1;

When you say select * from emp where rownum >1; it will not return any
rows beacuse every row has got rownum value = 1.

Try rownum=1 in the above query u will notice that all rows will be
returned.

I think you mean "every *first* row". So if first row is filtered out
(like in the example), then every row will potentially be the first, and
therefore have rownum = 1, but not the second row.

/Bent