MySQL ORDER BY IN()

Question

I have a PHP array with numbers of ID's in it. These numbers are already ordered.

Now i would like to get my result via the IN() method, to get all of the ID's.

However, these ID's should be ordered like in the IN method.

For example:

IN(4,7,3,8,9)  

Should give a result like:

4 - Article 4  
7 - Article 7  
3 - Article 3  
8 - Article 8  
9 - Article 9

Any suggestions? Maybe there is a function to do this?

Thanks!

Underwent

What are the other data associated with those numbers? It might give us a clue why you want it in that order. – Trenatrenail

Answer 1

I think you may be looking for function FIELD -- while normally thought of as a string function, it works fine for numbers, too!

ORDER BY FIELD(field_name, 3,2,5,7,8,1)

Centripetal

Exactly. I was just looking in Paul Dubois's MySQL Cookbook and found it: SELECT id, name FROM articles WHERE id IN(7,4,21) ORDER BY FIELD(id, 7,4,21) Thanks! – Underwent

Darn you people are fast. ;-) – Codicil

@Henk, happy to have helped you, but an acceptance w/o an upvote is a really peculiar occurrence on SO -- any reason you didn't upvote me?-) – Centripetal

That's really clever. I wouldn't have thought to put FIELD anywhere after the WHERE clause, for fear that it would throw a syntax error or that the DB would get confused on which 'id' to use. But it makes sense, after my brain unexploded. – Feme

@Anthony, happy to have expanded your brain -- though it just makes it a deeper and darker mystery to me, as to why nobody's upvoting this!-) Peculiar indeed...:-). – Centripetal

Now this is a first class solution +1 – Gadfly

Answer 2

You could use FIELD():

ORDER BY FIELD(id, 3,2,5,7,8,1)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

It's kind of an ugly hack though, so really only use it if you have no other choice. Sorting the output in your app may be better.

Codicil

Answer 3

Standard SQL does not provide a way to do this (MySQL may, but I prefer solutions that are vendor-neutral so I can switch DBMS' at any time).

This is something you should do in post-processing after the result set is returned. SQL can only return them in an order specified in the "order by" clause (or in any order if there's no such clause).

The other possibility (though I don't like it, I'm honor-bound to give you the choice) is to make multiple trips to the database, one for each ID, and process them as they come in:

select * from tbl where article_id = 4;
// Process those.
select * from tbl where article_id = 7;
// Process those.
: : : : :
select * from tbl where article_id = 9;
// Process those.

Rossetti

You can do it in standard SQL using CASE col WHEN *1st-val* THEN 1 WHEN *2nd-val* THEN 2 ... END – Hist

... not that I'd recommend such a thing! – Hist

Answer 4

You'll just need to give the correct order by statement.

SELECT ID FROM myTable WHERE ID IN(1,2,3,4) ORDER BY ID

Why would you want to get your data ordered unordered like in your example?

If you don't mind concatening long queries, try that way:

SELECT ID FROM myTable WHERE ID=1
UNION
SELECT ID FROM myTable WHERE ID=3
UNION
SELECT ID FROM myTable WHERE ID=2

Viehmann

Thanks. But this will not do the trick: SELECT id, name FROM articles WHERE id IN(7,4,21) ORDER BY id Should show 7,4,21. But instead it will show: 4,7,21... – Underwent

Union does not guarantee order. – Rossetti

Right, therefore my second shot, will give the wanted 'order' but is not the sweetest thing. – Viehmann

I think you misunderstood my comment, @BeowulfOF. There is nothing in the SQL spec that says the id=1 rows will be returned before the id=2 ones. UNION just unions the selects, it doesn't control what order they come in. – Rossetti

Not quite, Pax, my answer was for Henks comment. Nevermind, i didn't wrote it explicitly. – Viehmann