SQL - retain ordering based on the query params

Question

I'm trying to perform a SELECT with an IN clause and I would like to be able to have the results returned in the same order as the elements in my list for the IN. For example:

SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932', ...);

and I would want them to come back in that same order. Ideally, it'd be great if I could have a statement like:

SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932', ...)
ORDER BY ('B123', 'B483', 'B100', 'B932', ...);

---

I've seen examples of queries using the CASE or DECODE keywords to define some sort of custom ordering. But, in all those examples, their ordering was for a predetermined set of options. Whereas, my ordering is completely dependent on what my user enters for their search criteria, so there could be a list of 2 options or a list of 100 to order by...

Any ideas? Some Oracle feature I don't know of, or some way to use CASE or DECODE for a dynamic set?

Answer 1

Insert the values into a temporary table and join your select to that.

You can then do a natural order on your temporary table column.

CREATE GLOBAL TEMPORARY TABLE sort_table (
  value       VARCHAR2(100),
  sort_order  NUMBER
) ON COMMIT DELETE ROWS;

INSERT INTO sort_table VALUES ('B123',1);
INSERT INTO sort_table VALUES ('B483',2);
... etc. ...

select * from mytable
inner join sort_table
on mytable.mycolumn = sort_table.value
order by sort_table.sort_order;

To clear the temporary table, just COMMIT.

Answer 2

you can try it will work fine. Check below sql:-

SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932') 
ORDER BY DECODE(order_no,'B123','1','B483','2','B100','3','B932','4');

Answer 3

I don't know if there is an elegant (or short) solution for this.

If you can build the query dynamically, the following should work:

WITH numbers AS (
   SELECT 1 as sort_order, 'B123' as order_no FROM DUAL
   union all
   SELECT 2 as sort_order, 'B483' as order_no FROM DUAL
   union all
   SELECT 2 as sort_order, 'B100' as order_no FROM DUAL
   union all
   SELECT 2 as sort_order, 'B932' as order_no FROM DUAL
)
SELECT orders.*
FROM numbers  
  LEFT JOIN orders ON orders.ord_no = numbers.ord_no
ORDER BY numbers.sort_order

Answer 4

If you want to go with DECODE to assign a numerical sort order:

SELECT ID FROM tbl WHERE ID IN (2,3,1)
ORDER BY DECODE(ID, 2, 1, 3, 2, 3)

Answer 5

You can concatenate your variables and order by instr on it like below. I e cannot vouch for the efficiency of this - but must be Okey.Your front end will obviously have to do a bit more work.But constructing a query like this can be open to sql Injection.

SELECT * FROM orders WHERE order_no IN ('B123', 'B483', 'B100', 'B932', ...)
ORDER BY 
  instr ('@B123@'|| '@B483@'||'@B100@'||'@B932@'||... ,'@'|| order_no||'@')

Answer 6

I made an answer here in a more recent question

https://stackoverflow.com/questions/14234748/preserve-rows-order-in-select-query-as-same-in-statement

My answer use a pipe row function so it doesn't need to use a temp table like the accepted answer here.

Answer 7

I'm not a Python guy, but here's how I'd do it in PHP and I hope you get the idea.

1. Build a string like this:

   $str = "('B123', 'B483', 'B100', 'B932', ...)";

   For the above, you can use a for loop or something to build a really long string.

2. Insert the string in the query like this:

$MyQuery = "SELECT * FROM orders WHERE order_no IN $str
ORDER BY $str";

Like I said, this is a PHP example, but I believe you get the idea.