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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
