How to sort results by conditions?

Question

I have lots of keywords - "b1,b2,b3,a1,z1" which later would be composed as a query condition to do a query from a database.

It's like this:

SELECT * 
  FROM sometable
 WHERE id IN (b1,b2,b3,a1,z1)

But the search results are a mess in order.

    ID
------------   
    a1
    b2
    b3
    b1
    z1

The question is how to make the search results are presented perfectly as b1,b2,b3,a1,z1

How do you receive these keywords? If this is a stored procedure, you may create a local variable table with an identity PK and then do a join against it and order by that field. — Andrew, Mar 02 '16 at 02:41
are you using any CSV splitter to split the key words into list ? — Squirrel, Mar 02 '16 at 03:00

Gordon Linoff · Answer 1 · 2016-03-02T03:22:02.747

1

One method is to use charindex():

ORDER BY CHARINDEX(',' + CAST(id AS VARCHAR(255)) + ',', ',b1,b2,b3,a1,z1,')

Another method is to use join:

SELECT s.*
FROM sometable s JOIN
     (VALUES ('b1', 1), ('b2', 2), ('b3', 3), ('a1', 4), ('z1', 5)
     ) v(id, priority)
     ON s.id = v.id
ORDER BY v.priority;

edited Mar 02 '16 at 03:22

answered Mar 02 '16 at 02:27

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · edited Mar 02 '16 at 02:53

0

If ID is always a character plus a single digit number you can use this. You need to adjust the numbers if they can contain more than 1 character or more than 1 digit numbers.

SELECT *
FROM sometable
ORDER BY SUBSTRING(ID, 1, 1)
, SUBSTRING(ID, 2, 1)

edited Mar 02 '16 at 02:53

chancrovsky

582
3
11

answered Mar 02 '16 at 02:40

artm

8,554
3
26
43

How to sort results by conditions?

2 Answers2