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
>
>
