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
I would probably fix this by changing your membershiptype column to an ENUM, like this:
ALTER TABLE yourtable MODIFY membershiptype ENUM('1', '2', '0');
ENUM columns return what's in the quotes in string context, but return the position number in numeric context. So,
SELECT * FROM yourtable ORDER BY membershiptype;
would display membershiptype as 1, 2, or 0, but sort them in the order you want (as '1' => 1, '2' => 2, '0' => 3). Also, as you would now be using the column itself, rather than a function of the column, it is possible for an index to help.
WARNING: Note, however, that if you do this, you will have to change any code that sets membershiptype (INSERT or UPDATE) to add quotes, at least in the case of 0. That is,
INSERT INTO yourtable (membershiptype) VALUES ('0');
instead of
INSERT INTO yourtable (membershiptype) VALUES (0);
This is because, if you assign a number, mysql assumes you want the value in that position, and position 0 is the special empty string error value. (The same will happen for 1 and 2, but they're in the "right" place.)
From your description, this may not apply to your case, but if the values 1, 2, and 0 code for something, you could create an ENUM column with the descriptive text in each position, ordered as you want. For example, if 1, 2, and 0 mean 'one year', 'two year', and 'expired', then you could create a new column with ENUM('one year', 'two year', 'expired'), set the new column's values using membershiptype, drop membershiptype, and rename the new column as membershiptype. The payoff would be that you could eliminate code to translate 1, 2, and 0 into text.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
