Keep order from 'IN' clause

Question

Is it possible to keep order from a 'IN' conditional clause?

I found this question on SO but in his example the OP have already a sorted 'IN' clause.

My case is different, 'IN' clause is in random order Something like this :

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField IN (45,2,445,12,789)

I would like to retrieve results in (45,2,445,12,789) order. I'm using an Oracle database. Maybe there is an attribute in SQL I can use with the conditional clause to specify to keep order of the clause.

What about this http://stackoverflow.com/questions/2185029/sort-by-order-of-values-in-a-select-statement-in-clause-in-mysql? — Rikesh, Jan 03 '13 at 13:34
I removed the references to PL/SQL. PL/SQL is **only** for stored procedures, functions and triggers. Everything else is "just" SQL in Oracle. — , Jan 03 '13 at 13:36
@Rikesh Can help but most answers are based on FIELD(). It seems to be "MySql function" — bAN, Jan 03 '13 at 13:37

score 22 · Accepted Answer · answered Jan 03 '13 at 13:35

22

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.

answered Jan 03 '13 at 13:35

David Aldridge

51,479
8
68
96

2

As you say, there's no defined order unless you provide an `ORDER BY` clause. That's as true for a sequence of `UNION ALL` separated `SELECT`s as for anywhere else. – Damien_The_Unbeliever Jan 03 '13 at 13:44
Yes, I think there may be a future Day of Reckoning (or Version of Reckoning) for those who rely on set processing order, just as there was for those who relied on implicit ordering in GROUP BY. – David Aldridge Jan 03 '13 at 15:07
1

Update: Version 12 can execute sets in parallel, so no implicit execution ordering is in place. – David Aldridge Jun 23 '15 at 11:26

score 8 · Answer 2 · answered Jul 10 '18 at 10:42

8

Decode function comes handy in this case instead of case expressions:

SELECT SomeField,OtherField
FROM TestResult 
WHERE TestResult.SomeField IN (45,2,445,12,789)
ORDER BY DECODE(SomeField, 45,1, 2,2, 445,3, 12,4, 789,5)

Note that value,position pairs (e.g. 445,3) are kept together for readability reasons.

answered Jul 10 '18 at 10:42

Pero

1,371
17
18

Works well with Oracle. – Kris Oct 13 '20 at 02:46

Hamlet Hakobyan · Answer 3 · 2013-01-03T14:19:42.643

3

Try this:

SELECT T.SomeField,T.OtherField
FROM TestResult T
 JOIN 
   (
     SELECT 1 as Id, 45 as Val FROM dual UNION ALL
     SELECT 2, 2 FROM dual UNION ALL
     SELECT 3, 445 FROM dual UNION ALL
     SELECT 4, 12 FROM dual UNION ALL
     SELECT 5, 789  FROM dual
   ) I
   ON T.SomeField = I.Val
ORDER BY I.Id

edited Jan 03 '13 at 14:19

answered Jan 03 '13 at 13:37

Hamlet Hakobyan

32,965
6
52
68

2

Thanks @Hamlet Hakobyan. But I don't need a 'FROM' keyword in the subselections? Just before UNION ALL?.. – bAN Jan 03 '13 at 13:59
`FROM clause` specify the source of data. Have you any source in subquery? No. – Hamlet Hakobyan Jan 03 '13 at 14:14
2

Shouldn't this have `from dual` since the specified database is Oracle? – Gordon Linoff Jan 03 '13 at 14:15
It doesn't work for me. I was thinking a from was mandatory (FROM DUAL when no datasource). – bAN Jan 03 '13 at 14:16

score 2 · Answer 4 · answered Jan 03 '13 at 14:20

2

There is an alternative that uses string functions:

with const as (select ',45,2,445,12,789,' as vals)
select tr.*
from TestResult tr cross join const
where instr(const.vals, ','||cast(tr.somefield as varchar(255))||',') > 0
order by instr(const.vals, ','||cast(tr.somefield as varchar(255))||',')

I offer this because you might find it easier to maintain a string of values rather than an intermediate table.

answered Jan 03 '13 at 14:20

Gordon Linoff

1,242,037
58
646
786

OK. But is can be very slow. – Hamlet Hakobyan Jan 03 '13 at 14:41
@HamletHakobyan . . . Not necessarily. There is overhead for joins and in many cases, string operations may perform better. Clearly, if you have lists with thousands of elements, the database should do a better job using joins. But for a handful, it is quite possible that string operations would actually be faster. – Gordon Linoff Jan 03 '13 at 14:58
2

Problem isn't in list. In your case engine can't use index for `where clause`. – Hamlet Hakobyan Jan 03 '13 at 15:08
@HamletHakobyan . . . True, if you have an index on this field. – Gordon Linoff Jan 03 '13 at 15:18

score 0 · Answer 5 · answered Jan 21 '18 at 03:39

I was able to do this in my application using (using SQL Server 2016)

select ItemID, iName
  from Items
      where ItemID in (13,11,12,1)
      order by CHARINDEX(' ' + Convert("varchar",ItemID) + ' ',' 13 , 11 , 12 , 1 ')

I used a code-side regex to replace \b (word boundary) with a space. Something like...

var mylist = "13,11,12,1";
var spacedlist = replace(mylist,/\b/," ");

Importantly, because I can in my scenario, I cache the result until the next time the related items are updated, so that the query is only run at item creation/modification, rather than with each item viewing, helping to minimize any performance hit.

score 0 · Answer 6 · answered Dec 22 '22 at 00:26

Pass the values in via a collection (SYS.ODCINUMBERLIST is an example of a built-in collection) and then order the rows by the collection's order:

SELECT t.SomeField,
       t.OtherField
FROM   TestResult t
       INNER JOIN (
         SELECT ROWNUM AS rn,
                COLUMN_VALUE AS value
         FROM   TABLE(SYS.ODCINUMBERLIST(45,2,445,12,789))
       ) i
       ON t.somefield = i.value
ORDER BY rn

Then, for the sample data:

CREATE TABLE TestResult ( somefield, otherfield ) AS
SELECT   2, 'A' FROM DUAL UNION ALL
SELECT   5, 'B' FROM DUAL UNION ALL
SELECT  12, 'C' FROM DUAL UNION ALL
SELECT  37, 'D' FROM DUAL UNION ALL
SELECT  45, 'E' FROM DUAL UNION ALL
SELECT 100, 'F' FROM DUAL UNION ALL
SELECT 445, 'G' FROM DUAL UNION ALL
SELECT 789, 'H' FROM DUAL UNION ALL
SELECT 999, 'I' FROM DUAL;

The output is:

SOMEFIELD	OTHERFIELD
45	E
2	A
445	G
12	C
789	H

fiddle

Keep order from 'IN' clause

6 Answers6

Linked

Related