How to Select MySQL Rows in The Order of in Clause

How to select mysql rows in the order of IN clause

Use the FIND_IN_SET function:

SELECT e.* 
  FROM EMPLOYEE e 
 WHERE e.code in (1,3,2,4) 
ORDER BY FIND_IN_SET(e.code, '1,3,2,4')

Or use a CASE statement:

SELECT e.* 
  FROM EMPLOYEE e 
 WHERE e.code in (1,3,2,4) 
ORDER BY CASE e.code
           WHEN 1 THEN 1 
           WHEN 3 THEN 2
           WHEN 2 THEN 3
           WHEN 4 THEN 4
         END

Sort the rows according to the order specified in WHERE IN clause

You should use "ORDER BY FIELD". So, for instance:

SELECT * FROM table WHERE id IN (118,17,113,23,72) 
ORDER BY FIELD(id,118,17,113,23,72)

Sort by order of values in a select statement in clause in mysql

Actually, this is better:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

heres the FIELD documentation:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function:

SELECT name, description, ...
FROM ...
WHERE id IN([ids, any order])
ORDER BY FIELD(id, [ids in order])

FIELD() will return the index of the first parameter that is equal to the first parameter (other than the first parameter itself).

FIELD('a', 'a', 'b', 'c')

will return 1

FIELD('a', 'c', 'b', 'a')

will return 3

This will do exactly what you want if you paste the ids into the IN() clause and the FIELD() function in the same order.

How do I select the first few rows when I have a clause where in mysql?

If you only want to look in the first 8 rows, you need to figure out their id_cat values, and then use IN with the subquery that finds them i.e.

SELECT *
FROM nazir_items_ishop_category
WHERE active = 1 
AND id_cat IN (SELECT id_cat 
               FROM nazir_items_ishop_category 
               ORDER BY id_cat ASC
               LIMIT 8)

If you can't use LIMIT in a subquery, you can use a JOIN to a derived table instead:

SELECT n1.*
FROM nazir_items_ishop_category n1
JOIN (SELECT id_cat,active 
      FROM nazir_items_ishop_category 
      ORDER BY id_cat ASC
      LIMIT 8) n2 ON n2.id_cat = n1.id_cat AND n2.active = 1

Why MySQL does not return rows in same order as appeared in IN clause?

If you want to preserve the order as in in clause , you may use order by field something as

select 
id, price from product 
where id IN (5552902, 5552737, 5552887, 5549436, 5552291, 5552897, 5550834, 5552794, 5548666, 5552621)
order by field(id,5552902, 5552737, 5552887, 5549436, 5552291, 5552897, 5550834, 5552794, 5548666, 5552621)
;

How order by clause works in mysql, ordering shows wierd behaviour

The issue with this is because there is a duplicated value that you specify for your order by aka its either Mr. or Mrs. there is no guaranteed select order with this because they are all Mr. or Mrs.

if you want to ensure that it is always going to be in a specific order then you should also include the primary key as a second ordering to keep it all the same.. aka

ORDER BY CON_PREFIX ASC, M_ID ASC

as it stands right now ORDER BY CON_PREFIX is giving you exactly what it should be giving you, the prefixes in ascending order, there is nothing related to the limit for causing this, you simply just havent told MySQL how else you want the data to be returned to you.

---

to respond to your PERSONAL OPINION edit in the question....
what you are describing is unreasonable, think of it this way any default order that would be used to pull out data is now gone because you are specifying an order by. if there wasn't any order by on the clause then mysql has a generic select pattern but again that is gone once you put an order by on the query

MySQL: Which is the row's order of SELECT * FROM table?

According to this DBA Exchange answer
https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

In the SQL world, order is not an inherent property of a set of
data. Thus, you get no guarantees from your RDBMS that your data will
come back in a certain order -- or even in a consistent order --
unless you query your data with an ORDER BY clause.

MySQL sorts the records however it wants without any guarantee of consistency.

But observing the store system algoritm seem according to the storage sequence ..

But for to be sure you must specify your desired order using ORDER BY. To do anything else is to set yourself up for unwelcome surprises.

Referring to the order of the row result then the order where no order by clause is defined is unpredictable by ANSI Definition of SQL. The rows are retrieved form the "storage system" whitout regarding a specific strategy. depende of the allocation algoritm of the space disk and the mapping of the segmentation on this disk space (tablespace)

Referring to the column sequence in select (*) these are show based on the position id in the column table

---

Related Topics