SubSelects are allowed in MySQL 4.1, but not in any earlier versions. If I recall what rownum is (a number generated to represent the rownum of the returned data set), it has no equivalent in MySQL, as the LIMIT clause is available.
Regards, Mike Hillyer www.vbmysql.com -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 9:08 AM To: Mike Hillyer; Dave Terrio; [EMAIL PROTECTED] Subject: Re: nested ORDER BY statements Hi, I am a bit new to mysql but have extensive experience with Oracle. Does MYSQL allow a select like: select data1,data2,name1 from (select data1,data2,name1,rownum from table order by time) table1 where rownum < 51 order by name1 Cheers, Ton ----- Original Message ----- From: "Mike Hillyer" <[EMAIL PROTECTED]> To: "Dave Terrio" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, June 04, 2003 4:40 PM Subject: RE: nested ORDER BY statements Aah, in that case I am not sure you can do what you need to do with a single query... A UNION may allow it. (SELECT * FROM table1 WHERE 1=0) UNION (SELECT * FROM table1 ORDER BY table1.time_created DESC LIMIT 50) ORDER BY table1.name; Or something to that effect may work. You may even be able to take away the first select and the union statement, I am not sure. Regards, Mike Hillyer www.vbmysql.com -----Original Message----- From: Dave Terrio [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 8:27 AM To: Mike Hillyer Subject: RE: nested ORDER BY statements Thanks for the suggestion Mike - unfortunately, that doesn't do what I want it to - that keeps the top 50 records ordered by time as all 50 times are unique... I want to grab the 50 most recent - then completely reorder them by name... Any ideas? Thanks, -David >===== Original Message From "Mike Hillyer" <[EMAIL PROTECTED]> ===== >Drop the second ORDER BY clause in favor of a comma. > >ORDER BY table1.time_created DESC, table1.name LIMIT 50 > >Regards, >Mike Hillyer >www.vbmysql.com > > >-----Original Message----- >From: Dave Terrio [mailto:[EMAIL PROTECTED] >Sent: Wednesday, June 04, 2003 7:38 AM >To: [EMAIL PROTECTED] >Subject: nested ORDER BY statements > > >Hello - I'm a relative newcomer to development with MySQL and am having >a >problem with ordering my query results... > >I first want to select the 50 most recently created records (with >respect to >my date field) and then order these with respect to another field (eg >name). >That way I'm always "ordering" the 50 most recent records. The problem >is, I >can't figure out how to do this - as MYSQl complains when I put 2 "ORDER >BY" >statements in a query such as in: > >order by table1.time_created desc limit 50 order by table1.name; > >Any suggestions? > >Thanks, >-David > > >-- >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] -- 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]
