Rownum in oracle?

[jatinkansagara]

I am using rownum in oracle but it not work fine.

Record in Test Table is 10
e.g select * from test where rownum<=5;

it display 5 records.
but


e.g select * from test where rownum>1;
then no record found is display.

Help to solve this problem
Thank in Advance..

[DarrylBurke]

ROWNUM in SQL

db

[Tolls]

Also read the link at the bottom of that page ("On a table's first five rows").
Because your first query above is essentially saying "give me five rows from the test table". It can return different results at different times.

[jatinkansagara]

Also read the link at the bottom of that page ("On a table's first five rows").
Because your first query above is essentially saying "give me five rows from the test table". It can return different results at different times.

following record for test table for first query...
1
2
3
4
5

[Tolls]

And?
What happens if you delete a row?
What happens if you add 100 rows?
Oracle can rejig the layout of rows in its memory as and when it feels a need.
Oracle does not guarantee the order rows are returned in without an ORDER BY statement, so that same query is essentially picking any 5 rows from that table.

[DarrylBurke]

You seem to have missed the point that rownum applies to the row number in the query result, not to the row number in the table.

Why do people have to repeat here what's expressed very clearly in the linked web page?

db

[Tolls]

You seem to have missed the point that rownum applies to the row number in the query result, not to the row number in the table.

Why do people have to repeat here what's expressed very clearly in the linked web page?

db

Indeed I don't think there is a concept of row number in Oracle (or any other RDBMS?) for how the data is stored. It's purely a result set construct, as you've said.

[DarrylBurke]

Visual FoxPro has a RECNO() function that returns the row number (record number, in VFP parlance) in the data table. So in VFP you can

SELECT * FROM Test WHERE RECNO() > 1

db

[Tolls]

Maybe I should have said "real RDBMS"...;)

[jatinkansagara]

select * from (select rownum r ,max_manual_per from test) where r >1;
this query work fine
so what mechanism for rownum concept in oracle...
can u explain me if u know....

[Tolls]

That works because the subquery has numbered the rows.
SELECT * FROM test WHERE rownum > 1 will return nothing.

And we have explained the concept.
Rownum is an identifier applied to the rows of a resultset.

[jatinkansagara]

but when we check rownum>1 then no row selected
but when we check rownum = 1 then one record found
so what is concept for that

[Tolls]

Since rownum is something assigned as the resultset is built, then nothing can have a rownum > 1, since the first row is rejected each time.

[jatinkansagara]

thank for answer....

[DarrylBurke]

Since rownum is something assigned as the resultset is built, then nothing can have a rownum > 1, since the first row is rejected each time.

That's clear from the link I posted at #2, which I found through Google and vetted to make sure it had a clear, concise explanation.

No more links for jatinkansagara, it's nothing but a waste of time.

db