On Wed, 24 Mar 2004 15:47:46 +0100, Henrik Schröder wrote:
> 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. :-)
There may be a cleaner method but could you add a field that's used
for sorting so that the sorted field has a value of 0 where membership
type is 1, 1 where it is 2, and 2 where it is 0? You could then ORDER
by this field but use the value from membershiptype for your other
purposes.
Tim
