From: Date: March 24 2004 9:17pm
Subject: Re: How to optimize ugly order by?
List-Archive: http://lists.mysql.org/mysql/162465
Message-Id: <c3sqd0$b40$1@sea.gmane.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Can you do:

SELECT
	IF(membershiptype = 0, 3, membershiptype) AS s1
FROM...
ORDER BY s1


Shouldn't that work?  Basically if mtype is 0 return 3 otherwise return 
the membership type... and sort by that field.

lemme know if that works or if I'm dead wrong ;)


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
>