MS SQL - Sorting based on conditions

1

The input and desired output is shown (It is an image, please click on it to get the full view)

1
1.png288×643 10.8 KB

The code is as follows:

CREATE TABLE k (id int, Msg varchar(1000), result int, time time)
INSERT INTO k VALUES ('1', 'a1', '8', '11:14:00')
INSERT INTO k VALUES ('1', 'qq', '8', '11:15:00')
INSERT INTO k VALUES ('1', 'd1', '8', '11:16:00')
INSERT INTO k VALUES ('1', 'e1', '8', '11:17:00')
INSERT INTO k VALUES ('1', 'f1', '8', '11:18:00')
INSERT INTO k VALUES ('1', 'h', '4', '11:19:00')
INSERT INTO k VALUES ('1', 'i', '4', '11:20:00')
INSERT INTO k VALUES ('1', 'm35', '4', '11:21:00')
INSERT INTO k VALUES ('1', 'n', '4', '11:22:00')
INSERT INTO k VALUES ('1', 'o', '4', '11:23:00')
INSERT INTO k VALUES ('1', 'p0', '4', '11:23:00')
INSERT INTO k VALUES ('2', 'a1', '4', '11:24:00')
INSERT INTO k VALUES ('2', 'p0', '4', '11:25:00')
INSERT INTO k VALUES ('2', 'qq', '4', '11:26:00')
INSERT INTO k VALUES ('2', 'c', '4', '11:27:00')
INSERT INTO k VALUES ('2', 'h', '4', '11:28:00')
INSERT INTO k VALUES ('2', 'o', '4', '11:29:00')
INSERT INTO k VALUES ('3', 'c', '4', '11:30:00')
INSERT INTO k VALUES ('3', 'qq', '4', '11:31:00')
INSERT INTO k VALUES ('3', 'e1', '4', '11:32:00')

select *from k order by
case
when Msg = 'a1' or Msg = 'b1' or Msg = 'b0' or Msg = 'c' or Msg = 'd1' or Msg = 'e1' or Msg = 'f1' or Msg = 'g' or Msg = 'h' or Msg = 'i' then 1
when Msg = 'p0' then 2
when Msg = 'p1' then 3
when Msg = 'qq' then 4
when Msg = 'i' then 5
when Msg = 'j' then 6
when Msg = 'k0' then 7
when Msg = 'k1' then 8
when Msg = 'l' then 9
when Msg = 'l1' then 10
else 11 end,Msg

But this code is not giving the desired output. In case if there is only one set of values in the 'id' column then it sorts the 'Msg' column accordingly as per the condition given in the code. But in the sample input there are 3 different sets of values in 'id' column (1,2 and 3). So when I execute the above code the sorting is done for the whole data. It is obvious because I did not give any conditions for grouping by 'id' column. Is there any solution to achieve the desired result? (Also I tried to fetch each line in the input table by using cursors but even there I was not able to put the conditions properly in the loop). Any help would be useful.

2

You'd probably want to sort by id first

3

Use a join to control sorting to make the code easier to write, use and maintain, and probably speed up performance as well (SQL is optimized for joins, since it does so many of them):

select *
from k 
left outer join (
    values('a1',1),('b0',1),('b1',1),('c',1),('d1',1),
          ('e1',1),('f1',1),('g',1),('h',1),('i',1),
          ('p0',2),
          ('p1',3),
          ('qq',4),
          ('i',5),
          ('j',6),
          ('k0',7),
          ('k1', 8),
          ('l',9),
          ('l1',10)
) as order_by(Msg,order_seq) on order_by.Msg = k.Msg
order by order_by.order_seq