You could do something like this for readability:
SELECT ...,if(membershiptype=0,3,membershiptype) as sortby ... ORDER BY sortby
This just creates a calculation column in the select that changes the value 0 to 3 and then you use the calculation column for the sort. I doubt this will speed things up, but I think it's easier to read.
You may want to look into what's involved in using enum or set for the field for the future.
On Mar 24, 2004, at 9:47 AM, Henrik Schröder wrote:
Hi all,
I have a table of users which contains a lot of information, and also a
column called membershiptype which can either be 0, 1, or 2. This table is
used a lot in various searches, and all results should be ordered by
membership type, but the order should not be 0-1-2, but instead 1-2-0.
Currently, this is achieved like this:
SELECT ... ORDER BY (membershiptype <> 1) ASC, (membershiptype <> 2) ASC,
login ASC
...which is rather ugly, and forces MySQL to create a temp table with the
calculated expressions and then re-sort the result using these. Since this
query is used a lot, it would be nice if I could get rid of this. I'm
completely stumped. Any ideas?
No, I did not choose the sort order. No, I can not change the values used.
Yes, it has to be this sort order. :-)
/Henrik Schröder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
