2

I have a table MY_TABLE with a primary key MY_PK. Then, I have a list of ordered primary keys, for example (17,13,35,2,9).

Now I want to retrieve all rows with these primary keys, and keep the order of the rows in the same way as the given list of keys.

What I was initally doing was:

SELECT * FROM MY_TABLE WHERE MY_PK IN (:my_list)

But then the order of the returned rows is random and does not correspond to the order of the given keys anymore. Is there a way to achieve that? The only thing I thought of is making many SELECT statements and concatenate them with UNION, but my list of primary keys can be very long and contain hundreds or even thousands of keys. The alternative I thought of was to reorder the rows afterwards in the application, but I would prefer a solution where this is not necessary.

Julian
  • 994
  • 1
  • 9
  • 19

3 Answers3

2

First, doing this with a union would not necessarily help. The ordering of rows in the result set is not guaranteed unless you have an order by clause.

Here is one solution, although it is inelegant:

with keys as (
     select 1 as ordering, 17 as pk from dual union all
     select 2 as ordering, 13 as pk from dual union all
     select 3 as ordering, 35 as pk from dual union all
     select 4 as ordering, 2 as pk from dual union all
     select 5 as ordering, 9 as pk from dual
   )
select mt.*
from My_Table mt join
     keys
     on mt.my_pk = keys.pk
order by keys.ordering
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

Oracle only guarantees the order of a result set if it is sorted with an explicit ORDER BY statement. So your actual question is, "how can I guarantee to sort my results into an arbitrary order?"

Well the simple answer is you can't. The more complicated answer is that you need to associate your arbitrary order with an index which can be sorted.

I will presume you're getting your list of IDs as a string. (If you get them as an array or something similarly table-like life is easier.) So first of all you need to tokenize your string. In my example I use the splitter function from this other SO thread. I'm going to use that in a common table expression to get some rows, and use the rownum pseudo-column to synthesize an index. Then we join the CTE output to your table.

with cte as
    ( select s.column_value as id
             , rownum as sort_order
       from table(select splitter('17,13,35,2,9') from dual) s 
    )
select yt.*
from your_table yt
where yt.id = cte.id
order by cte.sort_order

Caveat: this is untested code but the principle is sound. If you do get compilation or syntax errors which you cannot resolve please include sufficient detail in the comments.

Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
  • Oracle, nothing, no RDBMS I'm aware of will guarantee the ordering of results unless an `ORDER BY` statement is provided. And can oracle not handle host-variable arrays? That's what I was presuming he was using (so no need to split, although the rest of the idea is sound). – Clockwork-Muse Sep 21 '12 at 15:22
1

The way to guarantee an order on your resultset is using ORDER BY, so what I would do is to insert in a temporary table with 2 columns your primary key and a secuencial ID which you would use later to make the ORDER BY. Your temporal table would be:

PrimaryKey   ID
-------------------
17            1
13            2
35            3
2             4
9             5

After that just using a join of your table and the temporal table on the PrimaryKey column and order by the ID column of your temporal table.

Carlos Cambón
  • 116
  • 4