Page 1 of 1
Sorting based on an IN
Posted: Mon Sep 03, 2007 11:36 am
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.
Posted: Mon Sep 03, 2007 1:53 pm
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.
Posted: Mon Sep 03, 2007 3:00 pm
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
Posted: Mon Sep 03, 2007 3:10 pm
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)
Posted: Mon Sep 03, 2007 3:10 pm
by Weirdan
Posted: Mon Sep 03, 2007 3:12 pm
by Weirdan
or field, as timvw said
Posted: Mon Sep 03, 2007 3:12 pm
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/ 
)
Posted: Tue Sep 04, 2007 2:02 am
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!