List:General Discussion« Previous MessageNext Message »
From:Andy Shellam Date:November 30 2008 10:42pm
Subject:Re: Result ordering
View as plain text  
Hi Morten,

I think this is valid in MySQL (it certainly is for SQL Server) but you 
can use a CASE statement directly in the ORDER BY clause.  Try something 
like this:

SELECT name
FROM   foo
WHERE  bar = 34
OR     baz > 100
ORDER BY CASE bar WHEN 34 THEN 0 ELSE 1 END ASC, baz DESC
LIMIT 5;

Regards,
Andy

Morten wrote:
>
> Hi, I'm retrieving the name of some records where either one of two 
> criteria are satisfied:
>
> SELECT name
> FROM   foo
> WHERE  bar = 34
> OR     baz > 100
> ORDER BY baz DESC
> LIMIT 5;
>
> I would like to sort that result set, such that the records matching 
> bar = 34 occur before records with baz > 100. I could do this using a 
> CASE:
>
> SELECT name, (CASE WHEN bar = 34 THEN 0 ELSE 1 END) AS rank
> FROM   foo
> WHERE  bar = 34
> OR     baz > 100
> ORDER BY rank ASC, baz DESC
> LIMIT 5;
>
> But I need the name DISTINCT, how can I accomplish this? I could wrap 
> the above select in a SELECT DISTINCT name FROM (SELECT ... ) but is 
> there a nicer way to use the "rank" than including it in the result set?
>
> Thanks!
>
> Morten
>
>

Thread
Result orderingMorten30 Nov
  • Re: Result orderingAndy Shellam30 Nov
    • Re: Result orderingMorten30 Nov
  • RE: Result orderingMartin Gainty1 Dec