CODEWITHSUNDEEP
phpmysqlselect
cesarcarlos
cesarcarlos

Reputation: 1405

Mysql query, custom order

I have the following mysql query:

SELECT 
       col1, 
       col2, 
       col3 
FROM 
       guests 
WHERE 
       active = 1 AND 
       guestId IN (233, 255, 254, 226, 182, 231, 113, 121)

When I get the results however, I get them ordered by guestId (which is indexed) in ascending order (starting with 121 and ending with 255). I need to show the results on my website in that specific order. Is there a way I can bypass the default order?

Thanks.

Upvotes: 1

Views: 60

Answers (2)

Kodlee Yin
Kodlee Yin

Reputation: 1089

Append ORDER BY guestId DESC to the query so it looks like this: 

SELECT 
    col1, 
    col2, 
    col3 
FROM 
    guests 
WHERE 
    active = 1 AND 
    guestId IN (233, 255, 254, 226, 182, 231, 113, 121) 
ORDER BY 
    guestId DESC

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562250

You have to specify the custom order in your ORDER BY clause:

SELECT col1, col2, col3 FROM guests 
WHERE active = 1 AND guestId IN (233, 255, 254, 226, 182, 231, 113, 121)
ORDER BY FIND_IN_SET(guestId, '233,255,254,226,182,231,113,121');

The builtin FIND_IN_SET() function returns an integer which is the position that the first argument finds its match in the list. Note the list is a single string argument containin a comma-separated list, whereas your IN() predicate needs a variable number of arguments. Also IIRC there must be no spaces in the list you give to FIND_IN_SET().

Upvotes: 2

Related Questions