CODEWITHSUNDEEP
sqlmysql
DivideByHero
DivideByHero

Reputation: 20345

Order By a field being equal to a specific value?

Let's say I have this MySQL query:

SELECT * FROM A WHERE x='abc' OR y=0;

How can I prioritize the rows so that cases where x='abc' are ordered FIRST? If y=0 but x!='abc', I want those rows to come after cases where x='abc'.

Can this be accomplished with a simple ORDER BY clause?

Thanks!

Upvotes: 15

Views: 10353

Answers (3)

Mchl
Mchl

Reputation: 62395

ORDER BY FIELD(x,'abc',x)

Upvotes: 0

gabe
gabe

Reputation: 1129

I would do it with a case statement:

SELECT *, 
    CASE WHEN column_one = 0 THEN 0 
         WHEN column_two = 'ADMIN' THEN 1 
    END AS multi_column 
FROM sometable
ORDER BY multi_column;

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171491

SELECT *
FROM A 
WHERE x='abc' 
    OR y=0
order by case when x='abc' then 0 else 1 end;

Upvotes: 20

Related Questions