SQL IN operator with ORDER based on columns value

Question

I have a requirement where I need to get rows using a list of string if it matches any value in the specified column.

SELECT * FROM assets WHERE name IN (:names)

But I also need to order some of its items that are guaranteed to exist from the above query. These are the names that should be in order, after that the rest can be in any order.

'BG123', 'AR234', 'QR45', 'DO456'

How can I order it based on these given value. I cannot use the PK since it is a random string. It should also work when the table is empty at first. Thanks

Additional sample:

I need to order these names as long as they exist, while the rest can be in any order and matching the names list at the same time.

Bon app
Consolidated Holdings
Around the horn

Please add a few rows of sample data and the expected result. — The Impaler, May 01 '22 at 16:40
Tag only the database that you use. MySql and SQLite dialects are not the same. — forpas, May 01 '22 at 17:13

score 1 · Answer 1 · answered May 01 '22 at 18:21

1

you can try this as well :

SELECT * FROM(
SELECT *,
CASE WHEN name = 'DO456' then 4
     WHEN name ='QR45' then 3
     WHEN name = 'AR234' then 2
     WHEN name = 'BG123' then 1
     else 0
     end as name_temp 
 FROM assets WHERE name IN (:names)
) a
order by name_temp desc;
from assets ) a;

answered May 01 '22 at 18:21

DB08

151
6

May I know what is the difference between your answer and NickW answer? – Bitwise DEVS May 02 '22 at 02:34
Logic wise both is same. – DB08 May 02 '22 at 06:41

score 0 · Answer 2 · answered May 01 '22 at 18:08

0

You would need to use a CASE statement that produced values in the order you want and then ORDER BY that CASE statement e.g.

…

ORDER BY
CASE WHEN name = 'BG123' then 1
     WHEN name = 'AR234' then 2
     WHEN name = 'QR45' then 3
     WHEN name = 'DO456' then 4
else 5 end

answered May 01 '22 at 18:08

NickW

8,430
2
6
19

Will this go beyond 5 item? – Bitwise DEVS May 02 '22 at 02:34
Isn't supposed to be 0 and not 1? – Bitwise DEVS May 02 '22 at 10:08
It will go to whatever the maximum number of of statements in a CASE statement is. Is what meant to be 0 not 1? – NickW May 02 '22 at 12:52

SQL IN operator with ORDER based on columns value

2 Answers2