title: SQL Server : sorting the values based on ID column
body: <p>The input and the desired output is shown below </p>
  
  <p><strong>Input</strong></p>
  
  <pre><code>id  Msg res time
  --------------------
  1   a1  8   11:14:00
  1   qq  8   11:15:00
  1   d1  8   11:16:00
  1   e1  8   11:17:00
  1   f1  8   11:18:00
  1   h   4   11:19:00
  1   i   4   11:20:00
  1   m35 4   11:21:00
  1   n   4   11:22:00
  1   o   4   11:23:00
  1   p0  4   11:23:00
  2   a1  4   11:24:00
  2   p0  4   11:25:00
  2   qq  4   11:26:00
  2   c   4   11:27:00
  2   h   4   11:28:00
  2   o   4   11:29:00
  3   c   4   11:30:00
  3   qq  4   11:31:00
  3   e1  4   11:32:00
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>id  Msg res time
  ---------------------
  1   a1  8   11:14:00
  1   d1  8   11:16:00
  1   e1  8   11:17:00
  1   f1  8   11:18:00
  1   h   4   11:19:00
  1   i   4   11:20:00
  1   p0  4   11:24:00
  1   qq  8   11:15:00
  1   m35 4   11:21:00
  1   n   4   11:23:00
  1   o   4   11:22:00
  2   a1  4   11:24:00
  2   c   4   11:27:00
  2   h   4   11:28:00
  2   p0  4   11:25:00
  2   qq  4   11:26:00
  2   o   4   11:29:00
  3   c   4   11:30:00
  3   e1  4   11:32:00
  3   qq  4   11:31:00
  </code></pre>
  
  <p>The code is below</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But this code is not returning 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.</p>