List:General Discussion« Previous MessageNext Message »
From:Morten Date:November 30 2008 10:33pm
Subject:Result ordering
View as plain text  
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