Keep Order from 'In' Clause

Keep order from 'IN' clause

There will be no reliable ordering unless you use an ORDER BY clause ..

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField IN (45,2,445,12,789)
order by case TestResult.SomeField
         when 45 then 1
         when 2  then 2
         when 445 then 3
         ...
         end

You could split the query into 5 queries union all'd together though ...

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField = 4
union all
SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField = 2
union all
...

I'd trust the former method more, and it would probably perform much better.

Preserve order of SQL WHERE IN() clause with nested SELECT

Try using JOIN instead:

SELECT t2.`itemname` 
FROM `tabletwo` AS t2
JOIN (    
   SELECT count(`itemid`) AS cnt, `itemid`
   FROM `tableone`
   WHERE `some_codition`="satisfied"
   GROUP BY `itemid`
) AS t1 ON t1.`itemid` = t2.`id`
ORDER BY t1.cnt DESC

You can create a derived table using the subquery of the IN operator and perform a JOIN to this table, so that you are able to use the COUNT in the ORDER BY clause of the main query.

How to keep order provided in in clause in Spring Data JPA or Hibernate

You can do that by JPA too but you will have to create a comma separated list of ids in the order you want. In your case you can keep same order.

@Query(value = "select i from ItemEntity i where i.secondaryId in :ids 
       order by FIND_IN_SET(i.secondaryId, :idStr)")
List<ItemEntity> itemsIn(@Param("ids") List<UUID> ids, @Param("idStr") String idStr);

To create comma separated list you can java 8 stream:

ids.stream().map(Object::toString).collect(Collectors.joining(","));

Example:

SELECT id FROM User WHERE id in (2,3,1) 
ORDER BY FIND_IN_SET(id,"2,3,1");

Result:

+----+
| id |
+----+
|  2 |
|  3 |
|  1 |
+----+

There is also one alternative using JPQL:

You can use ,,FIELD'' instead of ,,FIND_IN_SET ''.

You can find the example here:
https://stackoverflow.com/a/65943906/15101302

Maintain order when using SQLite WHERE-clause and IN operator

You can create a CTE that returns 2 columns: the values that you search for and for each value the sort order and join it to the table.

In the ORDER BY clause use the sort order column to sort the results:

WITH cte(id, ticker) AS (VALUES (1, 'GDBR10'), (2, 'GDBR5'), (3, 'GDBR30'))
SELECT t.* 
FROM tbl t INNER JOIN cte c
ON c.ticker = t.ticker
ORDER BY c.id

See the demo.

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 you keep the order using SELECT WHERE IN()?

Use FIND_IN_SET:

ORDER BY FIND_IN_SET(id, '56,55,54,1,7')

Select records with order of IN clause

You have a couple of options. Simplest may be to put the IN parameters (they are parameters, right) in a separate table in the order you receive them, and ORDER BY that table.

Maintaining order in MySQL IN query

As the other answer mentions: the query you posted has nothing about what order you'd like your results, just which results you'd like to get.

To order your results, I would use ORDER BY FIELD():

SELECT * FROM foo f where f.id IN (2, 3, 1)
ORDER BY FIELD(f.id, 2, 3, 1);

The argument list to FIELD can be variable length.

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

---

Related Topics