4

When querying the db for a set of ids, mysql doesnot provide the results in the order by which the ids were specified. The query i am using is the following: 

SELECT id ,title, date FROM Table WHERE id in (7,1,5,9,3)

in return the result provided is in the order 1,3,5,7,9.

How can i avoid this auto sorting

codaddict
  • 445,704
  • 82
  • 492
  • 529
Nauman Bashir
  • 1,732
  • 3
  • 18
  • 26
  • I once receive this request from one of my company developer, I simply reply `Seems you have the precise information of 7,1,5,9,3, why you still need to sort it again ???` – ajreal Nov 16 '10 at 16:52
  • @ajreal i get these ids from another server. n i need to query the db in the relevant order. i dont wanna sort it, the db returns in a sorted way.. i wanna avoid the sorting :) – Nauman Bashir Nov 19 '10 at 13:14
  • .. ARGGGGG...you already have these IDs in the ORDER of `7,1,5,9,3`, why you need to sort it again ?? Two solution, `query one id by id`. `Or use a place holder array that is your original order and fetch the results from database in whatever order, loop the place holder array and set it accordingly`. You already has the chicken, why you still wondering how the egg laying ? – ajreal Nov 19 '10 at 13:21

4 Answers4

12

If you want to order your result by id in the order specified in the in clause you can make use of FIND_IN_SET as:

SELECT id ,title, date 
FROM Table 
WHERE id in (7,1,5,9,3)
ORDER BY FIND_IN_SET(id,'7,1,5,9,3')
codaddict
  • 445,704
  • 82
  • 492
  • 529
  • Creative solution. Is this likely to perform well on large sets? – Asaph Nov 16 '10 at 15:27
  • I think I see a potential problem with this solution. What if the list contains double digit numbers and a search for a single digit number that happens to be a substring of a double digit number yields the wrong index? Seems like it would be broken in that case. – Asaph Nov 16 '10 at 15:28
  • 1
    @Asaph: It works for large set, the *well* part needs to be seen. About your 2nd comment: I've tried it on double digit numbers and it seems to work fine. – codaddict Nov 16 '10 at 15:36
  • I now understand that it will work with double digit numbers due to the fact that commas are delimiters in the string. I got this from reading the docs: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set – Asaph Nov 16 '10 at 15:41
  • well i used ORDER BY FIELD(id, {values}).it worked but the only problem i have is the performance issues. – Nauman Bashir Nov 19 '10 at 13:17
  • really good reply @codaddict compared to ajreal who is just on a rant about everything ! thanks again. – Hittz Oct 11 '13 at 10:00
  • this solution doesnt work for me, any special version? – jpganz18 Dec 28 '18 at 00:31
3

There is no auto-sorting or default sorting going on. The sorting you're seeing is most likely the natural sorting of rows within the table, ie. the order they were inserted. If you want the results sorted in some other way, specify it using an ORDER BY clause. There is no way in SQL to specify that a sort order should follow the ordering of items in an IN clause.

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • 4
    More accurately, there is no way to specify ordering in the WHERE clause. – JohnFx Nov 16 '10 at 15:23
  • @JonhFx: +1 Thanks for broadening the scope of my statement. – Asaph Nov 16 '10 at 15:25
  • read the question, try it on your query.. it sorts when you specify the in keyword.. anyways.. i just added ORDER BY FIELD(id, {values}) in the end, and it worked.. – Nauman Bashir Nov 19 '10 at 13:19
  • @Nauman Bashir: I did re-read the question and stand by my answer. There is no sorting going on without an `ORDER BY`. You're getting the results back in the order that they happen to be present in the table, which in your case happens to be in ascending order of the id column. But this simply isn't true in the general case. In any case, your solution that uses `ORDER BY FIELD` is a good one. Stick with it. – Asaph Nov 30 '10 at 06:22
1

The WHERE clause in SQL does not affect the sort order; the ORDER BY clause does that.

If you don't specify a sort order using ORDER BY, SQL will pick its own order, which will typically be the order of the primary key, but could be anything.

If you want the records in a particular order, you need to specify an ORDER BY clause that tells SQL the order you want.

If the order you want is based solely on that odd sequence of IDs, then you'd need to specify that in the ORDER BY clause. It will be tricky to specify exactly that. It is possible, but will need some awkward SQL code, and will slow down the query significantly (due to it no longer using a key to find the records).

If your desired ID sequence is because of some other factor that is more predictable (say for example, you actually want the records in alphabetical name order), you can just do ORDER BY name (or whatever the field is).

If you really want to sort by the ID in an arbitrary sequence, you may need to generate a temporary field which you can use to sort by:

SELECT *,
CASE id
  WHEN 7 THEN 1
  WHEN 1 THEN 2
  WHEN 5 THEN 3
  WHEN 3 THEN 4
  WHEN 9 THEN 5
END AS mysortorder
FROM mytable
WHERE id in (7,1,5,9,3)
ORDER BY mysortorder;
Spudley
  • 166,037
  • 39
  • 233
  • 307
  • well.. thanks for the info.. but i am generating the query on run time.. so this would be a bit lengthy.. i just added ORDER BY FIELD(id, {values}) at the end of the query.. im just worried about the performance issues – Nauman Bashir Nov 19 '10 at 13:21
0

The behaviour you are seeing is a result of query optimisation, I expect that you have an index on id so that the IN statement will use the index to return records in the most efficient way. As an ORDER BY statement has not been specified the database will assume that the order of the return records is not important and will optimise for speed. (Checkout "EXPLAIN SELECT")

CodeAddicts or Spudley's answer will give the result you want. An alternative is assigning a priority to the id's in "mytable" (or another table) and using this to order the records as desired.

Jaydee
  • 4,138
  • 1
  • 19
  • 20