how can i order results by in condition with oracle-CodePudding

My codes like;

SELECT * FROM TABLE WHERE FNO IN(1,8,3,7,11,2)

this query result like

How can i get result like my in condition. Like;

CodePudding user response：

You can use a combination of ORDER BY and CASE WHEN, as example:

SELECT fno
FROM table
WHERE fno IN (1,8,3,7,11,2)
ORDER BY CASE fno
WHEN 1 THEN 1
WHEN 8 THEN 2
WHEN 3 THEN 3
WHEN 7 THEN 4
WHEN 11 THEN 5
WHEN 2 THEN 6
END

See this example working here: db<>fiddle

CodePudding user response：

You can order using DECODE:

SELECT *
FROM yourTable
WHERE FNO IN (1, 8, 3, 7, 11, 2)
ORDER BY DECODE(FNO, 1, 1, 8, 2, 3, 3, 7, 4, 11, 5, 2, 6);

If you expect many values each of which would require a custom sorting level, then you should maintain a second table which stores this information:

Table: FNO_SORT

FNO | pos
1   | 1
2   | 6
3   | 3
7   | 4
8   | 2
11  | 5

Now we can join to this table and then order using the pos field:

SELECT t.*
FROM yourTable t
INNER JOIN FNO_SORT f ON f.FNO = t.FNO
WHERE t.FNO IN (1, 8, 3, 7, 11, 2)
ORDER BY f.pos;

CodePudding user response：

Put the values inside a collection and then generate a row number for the collection indexes and order by that:

SELECT t.*
FROM   table_name t
       INNER JOIN (
         SELECT ROWNUM AS rn,
                COLUMN_VALUE AS value
         FROM   TABLE(SYS.ODCINUMBERLIST(1, 8, 3, 7, 11, 2))
       ) v
       ON (t.fno = v.value)
ORDER BY v.rn;

Which, for the sample data:

CREATE TABLE table_name (fno, item) AS
SELECT LEVEL, 'Item' || LEVEL FROM DUAL CONNECT BY LEVEL <= 12;

Outputs:

FNO ITEM

1 Item1

8 Item8

3 Item3

7 Item7

11 Item11

2 Item2

FNO	ITEM
1	Item1
8	Item8
3	Item3
7	Item7
11	Item11
2	Item2

db<>fiddle here