title: Sort the data based on a specific value SQL
body: <p>I am stuck with a requirement and I tried a part of code but works for some extent but fails to sort as per my requirement.</p>
  
  <p><strong>Requirement</strong></p>
  
  <p>If I have few rows with grouped by tasks</p>
  
  <pre><code>Name    RowToBeSorted
  
  task1   1
  task1   1
  task1   1
  task2   3
  task2   3
  task2   3
  task3   2
  task3   2
  task3   2
  task4   NULL
  task4   NULL
  task4   NULL
  </code></pre>
  
  <p>Here is my requirement, the sort order should display the group of data based on the column number which I showed above for Ex. if the column value says <strong>8</strong> then that data should be positioned in 8th sort <strong>GroupBy</strong> position, if it the column value is <strong>5</strong> then that set of data should be positioned on 5th sort <strong>GroupBy</strong> position. Now as per table structure <strong>task3</strong>'s column value is <strong>2</strong> then that group data should be sorted in 2nd position irrespective of other order, so output should looks like.</p>
  
  <pre><code>task1   1
  task1   1
  task1   1
  task3   2 &lt;-- positioned at 2nd group by
  task3   2 &lt;-- positioned at 2nd group by
  task3   2 &lt;-- positioned at 2nd group by
  task2   3
  task2   3
  task2   3
  task4   NULL
  task4   NULL
  task4   NULL
  </code></pre>
  
  <p>I have sorted the data based on the column value however problem is when the column can have 0 or NULL value which comes to the top and changes the position of the data which is not good.</p>
  
  <p>whatever the data I get from the query same will be reflected in Crystal Report further.</p>
  
  <p><strong>PS</strong></p>
  
  <p>If two or more set of data have same sort number it should be sorted based on Names which I have already taken care, I just need to position the row based on the value of the respective column which contain NULL too.</p>
  
  <p>Please let me know If I am clear enough.</p>
  
  <p><strong>UPDATE 1:</strong></p>
  
  <p>I tried and here is what I am getting. I still see NULL at the beginning and pushing other data below giving me wrong position, am I doing something wrong.</p>
  
  <pre><code>SELECT 
   -- few other columns
   SUBSTRING(UDF.[R/L], PATINDEX('%[0-9]%', UDF.[R/L]), LEN(UDF.[R/L])) RL_Num
  ,QEmployeeAll.FirstName + ' ' + QEmployeeAll.LastName AS EmployeeName
  from tableName
  WHERE SchedData.AssignDate = @Date
  GROUP BY QEmployeeAll.FirstName ,QEmployeeAll.LastName, AssignDate
  ,CASE WHEN SUBSTRING(UDF.[R/L], PATINDEX('%[0-9]%', UDF.[R/L]), LEN(UDF.
   [R/L])) IS NULL THEN 1 ELSE 0 END 
      ORDER BY RL_Num, EmployeeName ;
  </code></pre>
  
  <p><strong>RL_Num</strong> is my sorted value.</p>
  
  <p>here is my output window:</p>
  
  <p><a href="https://i.stack.imgur.com/ZlePr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZlePr.png" alt="enter image description here"></a></p>
  
  <p><strong>UPDATE 2:</strong></p>
  
  <p>I figured out what I did wrong with your code, thanks it helped me and now I could able to sort as per your snippet, however I still not achieved my requirement, here is the screen shot and explanation.</p>
  
  <p><a href="https://i.stack.imgur.com/DbB9e.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DbB9e.png" alt="enter image description here"></a></p>
  
  <p>if you see the first 3 rows are correct since sort order is <strong>1</strong>, next 3 rows valued <strong>3</strong> so that should be placed after NULL values(<strong>RENAMAED07-19</strong>) since only goal is it doesn't matter with the NULL or valid value, we only have to place those set of data in specified row. The last row will be in bottom since it is different task and sorted based on Name.
  so the output should be something like this.</p>
  
  <pre><code>RowsToBeSorted
  1
  1
  1
  NULL &lt;-- This position is irrelevant to me
  NULL &lt;-- This position is irrelevant to me
  NULL &lt;-- This position is irrelevant to me
  3 &lt;-- since value is 3rd position
  3 &lt;-- since value is 3rd position
  3 &lt;-- since value is 3rd position
  NULL &lt;-- This position is irrelevant to me
  </code></pre>
  
  <p>Let me know if it clear enough.</p>