6

i used a mysql query where there is "where id in ("22,20,21") " but when i get the result its like array ordered in asecending order ie 20,21,20

but i need it in the same order as i gave the clause

Anish Joseph
  • 1,026
  • 3
  • 10
  • 24
  • possible duplicate of [How can I order a query result same as the id specified in the WHERE condition?](http://stackoverflow.com/questions/3984531/how-can-i-order-a-query-result-same-as-the-id-specified-in-the-where-condition) – deceze Nov 30 '10 at 06:14
  • i'm agreeing with @deceze that it is a duplicate. hadn't seen your answer or probably would've pointed him in that direction. – nathan gonzalez Nov 30 '10 at 06:20

5 Answers5

18

i think you should be able to use the FIELD keyword like so:

SELECT * FROM table
WHERE id in (22,20,21)
ORDER BY FIELD(id,22,20,21);

this is mysql specific, and seems magical, but it works.

nathan gonzalez
  • 11,817
  • 4
  • 41
  • 57
  • 5
    To explain some of the magic... What's happening here is that the FIELD function looks up `id` in the remaining parameters passed to it, and returns a number. So if id=22, FIELD(...) returns 0, and if id=20, it returns 1. – Michael Kopinsky Nov 30 '10 at 06:22
  • 1
    @Michal Kopinsky, i've never really found good documentation on this function. care to share your source of magical knowledge? ;) – nathan gonzalez Nov 30 '10 at 06:29
  • @nathan It's right here: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_field – deceze Nov 30 '10 at 06:34
  • This isn't even what I'm looking for, but kudos on an awesome little tip. – Scott Flack Jun 28 '13 at 03:44
2

SQL results are unordered unless given an explicit ordering; the order won't be picked up from the in clause.

Since the IDs are neither ASC nor DESC either, you'd have to ORDER BY something else; how are you determining the 22,20,21 order in the first place?

Adam Vandenberg
  • 19,991
  • 9
  • 54
  • 56
  • select id,dets from details where id in(22,21,20,19) – Anish Joseph Nov 30 '10 at 06:15
  • i am taking that ids from other table which is a mapping table – Anish Joseph Nov 30 '10 at 06:15
  • this is actually not true in mysql. – nathan gonzalez Nov 30 '10 at 06:16
  • @Anish Then you should fetch both together in a `JOIN` query and `ORDER BY mapping_table.the_order_id`. – deceze Nov 30 '10 at 06:17
  • 1
    For a MySQL-specific solution, see @nathan's answer below. For a general solution, like @deceze says, perhaps combine the two queries into one and order by the mapping table. – Adam Vandenberg Nov 30 '10 at 06:18
  • not like that.. i got ids from other table where i am storing the ids of the details . and using that array to avoid recursive calls to mysql i used where in clause – Anish Joseph Nov 30 '10 at 06:18
  • 1
    @Anish Not sure what you mean by "recursive calls". If you could describe your situation in more detail, I'm sure we could come up with an elegant query. Preemptive strike: Please update your question with any new information. – deceze Nov 30 '10 at 06:21
2

As mentioned... WHERE clause doesn't do anything with ordering of result set. Use MySQL built-in FIELD function in ORDER BY clause to specify how the result set should be ordered:

... WHERE id IN (22, 20, 21) ORDER BY FIELD(id, 22, 20, 21) ...
Crozin
  • 43,890
  • 13
  • 88
  • 135
0

You cannot specify the order results will be returned in in a WHERE clause. If you want to specify order, you need to add an ORDER BY clause to your query. For a solution that uses FIND_IN_SET see this answer.

Community
  • 1
  • 1
Asaph
  • 159,146
  • 25
  • 197
  • 199
0

In general, the database system returns the results in whatever order it feels like. If you want it to be ordered, you have to tell it. This blog post provides one method for doing so; you may be able to find other solutions online.

Michael Kopinsky
  • 884
  • 1
  • 7
  • 14