CODEWITHSUNDEEP
sqloracle-database
user2917239
user2917239

Reputation: 898

Oracle DB order by IN

This question is for Oracle DB.

I have a set of values that are primary keys and want to get additional corresponding values for those primary keys. The following query works for that:

SELECT value1, value2, value3 
FROM mytable 
WHERE value1 IN ( 13548, 65435, 56455 )

However, that query returns the rows in a different order and I would like to have a query return them in the order listed in the IN parenthesis. Is it possible to do so with Oracle DB?

Upvotes: 0

Views: 58

Answers (3)

user330315
user330315

Reputation:

There is a way to do it:

with id_list (id, sort_order) as 
(
  select 13548, 1 from dual
  union all
  select 65435, 2 from dual
  union all 
  select 56455, 3 from dual
)
SELECT value1, value2, value3 
FROM mytable 
  JOIN id_list ON id_list.id = mytable.value1
ORDER BY id_list.sort_order

Oracle's lack of a values row constructor makes this a bit ugly to write, but it essentially does what you want. And you don't need to change the order by when you add new values.

Upvotes: 2

GriffeyDog
GriffeyDog

Reputation: 8386

It would be a cumbersome order by, but you could do something like this:

order by case value1 when 13458 then 1 when 65435 then 2 when 56455 then 3 end

Upvotes: 2

Vulcronos
Vulcronos

Reputation: 3456

You need an order by clause.

If you just order by value1 then the results will be in numerical order, 13548, 56455, 65435.

If you want the columns to be returned in a defined, non numerical order you can fake out the order by clause.

ORDER BY CASE WHEN VALUE1 = 65435 THEN 20000 ELSE VALUE1 END

This makes makes the 65k look like 20k only when ordering so that it will end up in between the other two values. You can add additional when statements to change the order of more values.

There is no default way to order by the in clause.

Upvotes: 1

Related Questions