Re: Select and Sort?
On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? IDCategory 6 biochemistry 62Commerce 95chemistry 2 Engg 87Ecommerce 7 math 45physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select and Sort?
Thanks for help. here is another solution for that... SELECT if (Category = 'others', '999', 0) as priority, ID, Category FROM TableName ORDER BY priority, Category Tariq Fred van Engen wrote: On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? ID Category 6 biochemistry 62 Commerce 95 chemistry 2 Engg 87 Ecommerce 7 math 45 physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select and Sort?
On Tue, Dec 10, 2002 at 04:12:22PM +0500, Tariq Murtaza wrote: Thanks for help. here is another solution for that... SELECT if (Category = 'others', '999', 0) as priority, ID, Category FROM TableName ORDER BY priority, Category That's right. It has the same problem regarding optimization and it gives you another column in your result set (which you may or may not want). Regards, Fred. Fred van Engen wrote: On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? ID Category 6 biochemistry 62 Commerce 95 chemistry 2 Engg 87 Ecommerce 7 math 45 physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Select and Sort?
SELECT ... WHERE Category 'others' UNION SELECT ... WHERE Category='others' -Original Message- From: Tariq Murtaza [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 7:12 AM To: Fred van Engen Cc: [EMAIL PROTECTED] Subject: Re: Select and Sort? Thanks for help. here is another solution for that... SELECT if (Category = 'others', '999', 0) as priority, ID, Category FROM TableName ORDER BY priority, Category Tariq Fred van Engen wrote: On Tue, Dec 10, 2002 at 03:32:13PM +0500, Tariq Murtaza wrote: I want to achieve recordset like below (sorting category by Name and including 'Others' at the end), I want to do that by single query ;), any idea??? ID Category 6 biochemistry 62 Commerce 95 chemistry 2 Engg 87 Ecommerce 7 math 45 physics 1 Software Engg 5 Others Try something like this: SELECT id, category from mytable order by concat(if(id=5,'B','A'),category); Only problem is that it won't optimize, so use this on small tables only. Regards, Fred. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php