How to Keep the Order Using Select Where In()

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')

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.

Retain the order while select in postgres

You cannot; the order will depend on the execution plan, the physical order of the table and other things.

You could do that with an array and an explicit ORDER BY:

SELECT u.id
FROM users AS u
   JOIN unnest(ARRAY[2,2,1]) WITH ORDINALITY AS arr(elem, ord)
      ON u.id = arr.elem
ORDER BY arr.ord;

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.

Preserving ORDER BY in SELECT INTO

What for?

Point is – data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

So it makes no sense to have a specific order on the insert as you try to achieve.

SQL 101: sets have no order.

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 can I keep the order of column values in a union select?

If you're not selecting from tables?

Then you could insert VALUES, instead of a select with unions.

insert into @QuestionOptionMapping (RateCode) values
 ('PD0116')
,('PL0090')
,('PL0091')
,('DD0026')
,('DD0025')

Or in your query, change all the UNION to UNION ALL.

The difference between a UNION and a UNION ALL is that a UNION will remove duplicate rows.
While UNION ALL just stiches the resultsets from the selects together.

And for UNION to find those duplicates, internally it first has to sort them.

But a UNION ALL doesn't care about uniqueness, so it doesn't need to sort.

A 3th option would be to simply change from 1 insert statement to multiple insert statements.
One insert per value. Thus avoiding UNION completely.

But that anti-golfcoding method is also the most wordy.

Preserve order of results with WHERE IN with jOOQ

You can use Field.sortAsc() for this:

create.select()
      .from(ITEM)
      .where(ITEM.ID.in(1, 3, 2, 4))
      .orderBy(ITEM.ID.sortAsc(1, 3, 2, 4))
      .fetchInto(Item.class);

Or, of course, to avoid repeating the list, use a local variable:

Integer[] ids = { 1, 3, 2, 4 };
create.select()
      .from(ITEM)
      .where(ITEM.ID.in(ids))
      .orderBy(ITEM.ID.sortAsc(ids))
      .fetchInto(Item.class);

See also this article about sort indirection here.

The order of a SQL Select statement without Order By clause

No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10 may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where conditions, group by clauses, unions, will be in whatever order the planner decides is most efficient to generate.

The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.

---

To put a finer point on it. RDBMS systems have the mandate to give you exactly what you asked for, as efficiently as possible. That efficiency can take many forms, including minimizing IO (both to disk as well as over the network to send data to you), minimizing CPU and keeping the size of its working set small (using methods that require minimal temporary storage).

without an ORDER BY clause, you will have not asked exactly for a particular order, and so the RDBMS will give you those rows in some order that (maybe) corresponds with some coincidental aspect of the query, based on whichever algorithm the RDBMS expects to produce the data the fastest.

If you care about efficiency, but not order, skip the ORDER BY clause. If you care about the order but not efficiency, use the ORDER BY clause.

Since you actually care about BOTH use ORDER BY and then carefully tune your query and database so that it is efficient.

---

Related Topics