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
