That query will work. The note is just dosn't matter, when building your SQL statment replace it with a varible. In PHP for instance you can do the following
$IDValue = 100 ; $Query = "SELECT id FROM myTable WHERE infoId IN (3) AND id $IDValue ORDER BY id ASC LIMIT 49,1" ; Job done. No idea why on earth you would ever want to use a nested select to do the job in the first place. Just seems to be a waste to me. Not to mention its probably going to be slower using nested selects then just "ORDER BY". ----- Original Message ----- From: "Victoria Reznichenko" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, September 23, 2003 2:41 PM Subject: Re: nested select alternative?? : "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] : : -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
