4

i have a mysql like this

SELECT * FROM table WHERE id IN(3245,76,3466,998,12984,4466,931,50,728)

is there any way i could get the results of the query ordered by the order the id's have in the IN clause?

Thanks a lot

UPDATE: i've just found this question covered here Althought, my IN clause does contain up to 5000 id's, so would the use of 'FIELD' the best solution out there?

Community
  • 1
  • 1
jmserra
  • 1,296
  • 4
  • 18
  • 34

4 Answers4

6

If you don't mind repeating the sequence of ids, using ORDER BY FIELD should do the trick:

SELECT * FROM table WHERE id IN(3245,76,3466,998,12984,4466,931,50,728)
ORDER BY FIELD (id,3245,76,3466,998,12984,4466,931,50,728)

As @vyegorov noted in the comments, for a large number of ids, you may have to create a temporary table to hold the actual order

CREATE TEMPORARY TABLE `temp_sort` (
    `id` INT NOT NULL ,
    `seq` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    PRIMARY KEY ( `seq` ) ,
    UNIQUE ( `id` )
);

INSERT INTO temp_sort (id) VALUES (3245),(76),(3466);

SELECT * FROM table  
INNER JOIN temp_sort ON (temp_sort.id=table.id)
ORDER BY temp_sort.seq;
nikoshr
  • 32,926
  • 33
  • 91
  • 105
  • Well thats what i was saying in the 'update' of the question, i'm not sure if repeating up to 5000 ids in the same query is a good idea in terms of speed – jmserra Apr 18 '12 at 17:02
  • 2
    If you have to order that many IDs, you should create a separate relation, like: `id INTEGER, seq_nr INTEGER`, join to this relation and user `ORDER BY extra.seq_nr` – vyegorov Apr 18 '12 at 17:05
  • Nice one. I wasn't aware of the fact that MySQL had that option. One thing to note would be that the FIELD() function will return a zero of the number isn't found, which means the unlisted items will be on top. Not a big deal unless you forget to add one number in both locations. – IAmTimCorey Apr 18 '12 at 17:06
  • hmm, sorry but didn't really got the INTEGER thing, could you please give me an example? Many thanks! – jmserra Apr 18 '12 at 17:14
  • @BiggsTRC described the solution that I meant in previous comment. – vyegorov Apr 18 '12 at 17:25
  • @jmserra Added an alternative solution based on the comments made by vyegorov and BiggsTRC – nikoshr Apr 18 '12 at 17:31
1

Since you have up to 5000 items in your IN clause, you really need to make a separate table that contains a list of your ids that you want to include. Put an order column in that table and then do an inner join and order by the order column. That solves both problems.

Like so:

SELECT table.*
FROM table
INNER JOIN tblIDs ON tblIDs.id = table.id
ORDER BY tblIDs.OrderValue

Your new table (tblIDs) would look like this:

id     OrderValue
3245        1
76          2
3466        3

By doing an INNER JOIN on the id columns, you will ensure that only those rows in your original table that have one of your listed id numbers will be displayed. The OrderValue column will allow you to easily and efficiently sort your query and it will also allow you to easily change the sort order without hacking a query apart.

IAmTimCorey
  • 16,412
  • 5
  • 39
  • 75
  • Well yes, that would be great, but i can't use a separate table, because the content of the IN clause is something returned by other queries, and each of those could be different and absolutely dynamic Thanks anyway! – jmserra Apr 18 '12 at 17:17
  • @jmserra Why couldn't you use the query as your joining table? Even if you had to UNION a couple query results into one query, it would still seem like it would be more efficient and easy to manage. – IAmTimCorey Apr 18 '12 at 18:25
0

Not unless you make those ids an attribute. And even then, it will order them asc/desc, not out of order like you have them.

Colleen
  • 23,899
  • 12
  • 45
  • 75
0

try doing this. 

SELECT your_column_name * FROM table WHERE id IN(3245,76,3466,998,12984,4466,931,50,728)      
ORDER BY your_column_name
Bhavin Ragha
  • 73
  • 1
  • 4
  • 11