Re: Sort Select by List
Dan Nelson wrote: --snip-- More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); Oops. I only read the replies and not the original post. Assuming there are many other values for the office field, you might want SELECT * FROM team WHERE office = 'Exec' UNION SELECT * FROM team WHERE office = 'VP' UNION SELECT * FROM team WHERE office = 'Dir'; Indeed! :) Especially if the table is of any decent size (I assumed that it was not *huge*). The UNION will give index accesses, the tricks on the ORDER BY will cause filesorts (although, they will still likely be in memory unless you the table is large, again). You should of course EXPLAIN and tune accordingly (with response times as well). Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Thanks Dan, It is a very small table. I really appreciate you help. Keith - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Mark Leith [EMAIL PROTECTED] Cc: Pintér Tibor [EMAIL PROTECTED]; [MySQL] mysql@lists.mysql.com Sent: Saturday, December 16, 2006 9:48 PM Subject: Re: Sort Select by List In the last episode (Dec 16), Dan Nelson said: In the last episode (Dec 16), Mark Leith said: Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC, office='Dir' DESC; More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); Oops. I only read the replies and not the original post. Assuming there are many other values for the office field, you might want SELECT * FROM team WHERE office = 'Exec' UNION SELECT * FROM team WHERE office = 'VP' UNION SELECT * FROM team WHERE office = 'Dir'; -- Dan Nelson [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]
Sort Select by List
Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith
Re: Sort Select by List
Hi Hal, I appreciate your help, but the order can not be alphabetical. It must be explicitly Exec, VP, Dir... Keith - Original Message - From: Hal Wigoda [EMAIL PROTECTED] To: Keith Spiller [EMAIL PROTECTED] Sent: Saturday, December 16, 2006 2:28 PM Subject: Re: Sort Select by List order by office On Dec 16, 2006, at 3:25 PM, Keith Spiller wrote: Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Keith Spiller írta: Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith order by right(office,1) or make an extra column for ordering t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC,office='Dir' DESC; Not many people know that you can order by literals as well ;) Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
In the last episode (Dec 16), Mark Leith said: Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC, office='Dir' DESC; More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
In the last episode (Dec 16), Dan Nelson said: In the last episode (Dec 16), Mark Leith said: Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC, office='Dir' DESC; More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); Oops. I only read the replies and not the original post. Assuming there are many other values for the office field, you might want SELECT * FROM team WHERE office = 'Exec' UNION SELECT * FROM team WHERE office = 'VP' UNION SELECT * FROM team WHERE office = 'Dir'; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
