Sorting based on an IN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Sorting based on an IN

Post by onion2k »

I have a query with an IN clause...

Code: Select all

SELECT  
`gwu_mp`.`mp_id`, 
`gwu_mp`.`display_name`, 
`gwu_mp`.`score`, 
`gwu_mp`.`score_change` 
FROM `gwu_mp` 
WHERE 1 
AND `gwu_mp`.`mp_id` IN (65,138,418,539,557,244,298,25,49,482,69,39,309,148,8,636, 326,234,419,77,279)
It works nicely apart from one small issue. I want the records in the order that they're in the IN clause. If I leave out any ORDER BY they're returning in the order of the primary key column. Is there a solution to order them in the order I want in SQL? I can do it in PHP easily, but that's messy.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Maybe a Stored Procedure that contained that set and sorted manually might do it. Otherwise I think you need to do it in PHP.
(#10850)
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

If you are using a fixed sort order or an order that doesn't change often you could add an extra column to sort by. If not then I would stick to PHP.

/josa
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Accidentally using MySQL?


ORDER BY FIELD(gwu_mp.mp_id, 65,138,418,539,557,244,298,25,49,482,69,39,309,148,8,636, 326,234,419,77,279)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

or field, as timvw said
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I'll be honest, a long time ago, it was Weirdan that learned me about the FIELD function (atleast, that's what i wrote in 2004 on my blog... http://www.timvw.be/custom-ordering-with-mysql/ ;))
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

timvw wrote:Accidentally using MySQL?

ORDER BY FIELD(gwu_mp.mp_id, 65,138,418,539,557,244,298,25,49,482,69,39,309,148,8,636, 326,234,419,77,279)
Why have I never used that before? It's really handy. Cheers!
Post Reply