CODEWITHSUNDEEP
mysql
TimSim
TimSim

Reputation: 4036

MySQL select that shows row with a specific value first

SELECT userid, name
FROM users 
WHERE nationality="US" 
ORDER BY name

Suppose I want a user with the userid=101 and nationality="US" to be shown first no matter what, and it's really important to do it using one query.

Upvotes: 2

Views: 1476

Answers (3)

AlexL
AlexL

Reputation: 1707

Try this:

SELECT * FROM (
    SELECT userid, name, IF(user = 101, 1 , 2) AS rank 
    FROM users WHERE nationality = "US"
) AS subselect ORDER BY subselect.rank ASC, subselect.name ASC

Upvotes: 0

Croises
Croises

Reputation: 18671

Or you can do that:

SELECT userid, name, (userid=101) AS test FROM users WHERE nationality="US" ORDER BY test DESC, name ASC

Upvotes: 0

Salih Erikci
Salih Erikci

Reputation: 5087

Try FIELD 

SELECT userid, name FROM users WHERE nationality="US" ORDER BY FIELD(user_id,101) DESC, name asc

Field Documentation

Upvotes: 6

Related Questions