----- Original Message ----- From: "Kevin A. Burton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 15, 2004 7:23 PM Subject: unions will full column names won't work in 4.1.7
> Not sure if this is a bug... probably should be. > > On 4.1.18 I can run: > > (SELECT * FROM FOO WHERE ID = 1) > UNION > (SELECT * FROM FOO WHERE ID = 2) > ORDER BY FOO.COL_A > > Which will work just fine > > However when I use this query on 4.1.7 I get > > ERROR 1250 (42000): Table 'ARTICLE' from one of the SELECTs cannot be > used in global ORDER clause > > I have to rewrite it to use: > > > (SELECT * FROM FOO WHERE ID = 1) > UNION > (SELECT * FROM FOO WHERE ID = 2) > ORDER BY COL_A > > .. see the change in the ORDER BY... I can't call if FOO.COL_A I have to > call it COL_A > I'm not sure if I'd call that a bug either; you may want to check the manual to see if FOO.COL_A is *supposed* to work. Perhaps the bug is that it works in 4.0.18 when it shouldn't? I don't know if this helps but another way to do ORDER BY for UNIONs is to say "Order by <n>" where 'n' is an integer describing which column of the result set you are sorting on. Therefore, if you are sorting on the 3rd and 5th columns of the result set (both ascending), you'd say 'Order by 3, 5'. I'm not crazy about this approach myself - it seems likely that some users will scratch their heads and wonder what the '3' and '5' refer to - but it is certainly very concise and eliminates the issue of having a table name in the ORDER BY. Remember, the ORDER BY always refers to the result of the UNION, never to any of the individual SELECTs that make up the full query. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
