List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 21 2009 10:44pm
Subject:Re: Select clause using from and to (like rownum in Oracle)
View as plain text  
>Is there anyway the SELECT query can be forced to use the "from" and "to"
>rownum parameters?

1st LIMIT arg = OracleFromArg
2nd LIMIT arg = OracleToArg - OracleFromArg + 1

so 'from 11 to 20' becomes LIMIT 11,10.

PB

-----

Anoop kumar V wrote:
> 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
>
>   
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com 
> Version: 8.5.409 / Virus Database: 270.13.63/2317 - Release Date: 08/21/09 06:04:00
>
>   

Thread
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