List:General Discussion« Previous MessageNext Message »
From:Anoop kumar V Date:August 21 2009 10:14pm
Subject:Select clause using from and to (like rownum in Oracle)
View as plain text  
Hi All,

I am facing a problem in porting an application written for oracle to run on

The application uses a sqlmap (ibatis) at the heart of which is basically a
file that defines all sql's used in the application. It is very well
organized this way. The application uses Oracle as the database. The problem
is that for pagination purposes the sql's written use rownum and accept 2
arguments - the "from" rownum and the "to" rownum.

I am trying to run the same application on my laptop that runs mysql. I have
migrated all data and all the sql queries work perfectly except the one that
use pagination and the rownum.

I know in mysql there is support for sql using the LIMIT clause, but the
LIMIT seems to take 2 arguments, the first one being the start rownum and
the second being the number of rows to output. I need the second to be the
"to" rownum. I have done a lot of googling, but apart from just putting a
rownum for the sql output there was no real usages for pagination purposes.

I cannot use the LIMIT as it is in mysql, because that would mean I would
have to change the application logic which I do not want to do. I also do
not want to install Oracle on my laptop, just too heavy.

I have found this to work except I am not sure how to pass a where clause
for the rownum part:

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t
I was trying something like:

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t where r.rownum between 10, 20;
or even
SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r,
user_approvers t where r.rownum=1;

I get the error:
ERROR 1054 (42S22): Unknown column 'r.rownum' in 'where clause'

Is there anyway the SELECT query can be forced to use the "from" and "to"
rownum parameters?

Thanks a lot for any help,

Select clause using from and to (like rownum in Oracle)Anoop kumar V22 Aug
  • Re: Select clause using from and to (like rownum in Oracle)Peter Brawley22 Aug