"Wajih-ur-Rehman" <[EMAIL PROTECTED]> wrote:
> I have this statement that works perfectly fine in Access and MSSQL: (In
> words, i first get the top 50 id values according to the where clause and
> then i get the maximum of those id values)
>
> "SELECT Max(id) FROM (select top 50 id from myTable where infoId in
> ( 3 ) AND id > 100 order by id ASC) AS t1 "
Max id is the last row in the result set of the query "select id from myTable where
infoId in ( 3 ) AND id > 100 order by id ASC LIMIT 50". So, you can write:
SELECT id FROM myTable WHERE infoId IN (3) AND id > 100 ORDER BY id ASC LIMIT
49,1;
>
> (Note that id > 100 changes in every iteration of the loop in my code)
Sorry, this note is not clear enough for me. What do you mean?
>
> I want to run a similar query in MYSQL....Is there *any* way to accomplish
> it ? I have tried the following query without any luck
>
> "SELECT Max(id) FROM (select id from myTable where infoId in
> ( 3 ) AND id > 100 order by id ASC LIMIT 50) AS t1 "
>
From v4.1 you can use derived table and the above query will work.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]