Sort the data based on a specific value SQL

Question

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.

Requirement

If I have few rows with grouped by tasks

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

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 8 then that data should be positioned in 8th sort GroupBy position, if it the column value is 5 then that set of data should be positioned on 5th sort GroupBy position. Now as per table structure task3's column value is 2 then that group data should be sorted in 2nd position irrespective of other order, so output should looks like.

task1   1
task1   1
task1   1
task3   2 <-- positioned at 2nd group by
task3   2 <-- positioned at 2nd group by
task3   2 <-- positioned at 2nd group by
task2   3
task2   3
task2   3
task4   NULL
task4   NULL
task4   NULL

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.

whatever the data I get from the query same will be reflected in Crystal Report further.

PS

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.

Please let me know If I am clear enough.

UPDATE 1:

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.

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 ;

RL_Num is my sorted value.

here is my output window:

UPDATE 2:

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.

if you see the first 3 rows are correct since sort order is 1, next 3 rows valued 3 so that should be placed after NULL values(RENAMAED07-19) 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.

RowsToBeSorted
1
1
1
NULL <-- This position is irrelevant to me
NULL <-- This position is irrelevant to me
NULL <-- This position is irrelevant to me
3 <-- since value is 3rd position
3 <-- since value is 3rd position
3 <-- since value is 3rd position
NULL <-- This position is irrelevant to me

Let me know if it clear enough.

Answer 1

Try this:

DECLARE @DataSource TABLE
(
    [Name] VARCHAR(12)
   ,[RowToBeSorted] INT
);


INSERT INTO @DataSource ([Name], [RowToBeSorted])
VALUES   ('task1', 1)
        ,('task1', 1)
        ,('task1', 1)
        ,('task2', 3)
        ,('task2', 3)
        ,('task2', 3)
        ,('task3', 2)
        ,('task3', 2)
        ,('task3', 2)
        ,('task4', NULL)
        ,('task4', NULL)
        ,('task4', NULL);


SELECT *
FROM @DataSource
ORDER BY CASE WHEN [RowToBeSorted] IS NULL THEN 1 ELSE 0 END
        ,[RowToBeSorted]
        ,[Name];

The idea is first to check if a RowToBeSorted IS NULL - if "yes" we are returning 1, otherwise 0 - in this way, NULL values will be always at the end but you will your sort by Name for them, too.