When I am using rownum and between keywords then the query doesn't return any rows. Can anyone explain the reason why query is not retrieving any rows?
select * from cus where rownum between 2 and 6;
I just want to check whether rownum will work when it is used with between keyword .So ,I just tried the above query to display the rows which are in between 2 and 6. But when I tried to execute the query, it doesn't retrieve any rows.
thanks in advance
Mysql doesnt have rownum.
If you are looking for oracle maybe you can try something like this:
select * from (select cus.*, rownum as row_num from cus) where row_num between 2 and 6;
or
select * from (select cus.*, rownum as row_num from cus) where row_num >1 and row_num <=6;
Oracle rownum starts at 1, so you will never get the first rownum if you say
between 2 and N
.It takes a row to "initiate" the rownum pseudocolumn sequence, so by eliminating rownum 1 in your criteria, you eliminate all rownums (or every row essentially has rownum
0
).Look at it like this. You don't get a ROWNUM until the database returns a row to you. The first row of any criteria will always be ROWNUM 1.
Now, the trick you can use is to use a subquery. Each subquery will have its own rownum, and if you alias it to another column name, you can preserve it into outer queries, and treat it however you like. So if you are looking to implement paging of a result set, you would normally alias rownum from inner results as rownum_ to an outer subquery to limit with BETWEEN.
But note, that the outer result set will have its own rownum, so you could do:
You will see
rownum
on the final result still starts at 1, but your inner result will haverownum_
starting at 2.That is completely wrong. Because,
ROWNUM
is a pseudo-column which increments to 2 only when it started atROW
one(random, of course). In your query, the predicateROWNUM BETWEEN 2 AND 6
is meaningless, since ROWNUM has not yet been assigned.Let's understand this step-by-step :
Understand how a SQL statement is interpreted. After the query is parsed, the filter is applied.
In your query, the filter
ROWNUM BETWEEN 2 AND 6
is meaningless, since, Oracle has not yet assignedROWNUM
1 as the first row is not yet fetched.When the first row is fetched, then
ROWNUM
is assigned as a pseudo-number. But the filter in your query directly points to rows between 2 and 6, which is absurd. So, no rows are returned.