Hi All, I am facing a problem in porting an application written for oracle to run on mysql.
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, Anoop
