CODEWITHSUNDEEP
sqloracle-database
bAN
bAN

Reputation: 13825

Keep order from 'IN' clause

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.

Upvotes: 27

Views: 32002

Answers (6)

MT0
MT0

Reputation: 168051

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

Upvotes: 0

Pero
Pero

Reputation: 1451

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.

Upvotes: 10

Regular Jo
Regular Jo

Reputation: 5510

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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.

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 23

Related Questions