CODEWITHSUNDEEP
sqlsql-serversql-order-by
thomsan
thomsan

Reputation: 483

How to select data order by sort value and sort NULL By name

I wrote following SQL query to select data from @tmp table variable.

SELECT @rowCount AS [row-count],
       t.[row-no] AS [row-no],
       t.[ServiceID] AS ServiceID,
       t.ServiceName AS ServiceName,
       t.[BranchServiceSortValue] AS SortValue,
       (CASE WHEN t.OptIn = 1 THEN 'Yes' ELSE 'No' END) AS OptIn
FROM   @tmp t
       INNER JOIN dbo.Category
            ON  Category.CategoryId = t.FkCategoryId
       INNER JOIN dbo.ServiceType
            ON  ServiceType.ServiceTypeId = t.FkServiceTypeId
WHERE  t.[row-no] >= @startRow
       AND t.[row-no] <= @endRow

       ORDER BY t.BranchServiceSortValue,t.serviceName

According to the data in @tmp table,my above query return following output.

| row-count | row-no | ServiceID | ServiceName | SortValue | OptIn |
|-----------|--------|-----------|-------------|-----------|-------|
| 24        | 4      | 1088      | AAB         | NULL      | No    |
| 24        | 5      | 1089      | AAC         | NULL      | No    |
| 24        | 6      | 1090      | AAD         | NULL      | No    |

| 24        | 1      | 1093      | GDGD        | 0         | Yes   |
| 24        | 7      | 1091      | EETETE      | 1         | Yes   |
| 24        | 8      | 1092      | CSCDF       | 2         | Yes   |
| 24        | 3      | 1086      | CXCX        | 3         | Yes   |
| 24        | 9      | 16        | ASA         | 4         | Yes   |
| 24        | 2      | 1087      | BFB         | 5         | Yes   |
| 24        | 10     | 7         | Mortgage    | 6         | Yes   |
| 24        | 11     | 17        | DDWW        | 7         | Yes   |
| 24        | 12     | 11        | IL          | 8         | Yes   |
| 24        | 13     | 5         | SAA         | 9         | Yes   |
| 24        | 14     | 9         | CD          | 10        | Yes   |

You can see according to my above query data rows are sorted by SortValue and when SortValue = NULL, those 3 rows sorted by its ServiceName,

But I need to displaySortValue = NULLrows at the bottom of the other rows.Its mean I need to display Null rows after the SortValue Not NULL data and SortValue = NULL should be display order by its ServiceName.

My Expected Output is:

| row-count | row-no | ServiceID | ServiceName | SortValue | OptIn |
|-----------|--------|-----------|-------------|-----------|-------|
| 14        | 1      | 1093      | GDGD        | 0         | Yes   |
| 14        | 7      | 1091      | EETETE      | 1         | Yes   |
| 14        | 8      | 1092      | CSCDF       | 2         | Yes   |
| 14        | 3      | 1086      | CXCX        | 3         | Yes   |
| 14        | 9      | 16        | ASA         | 4         | Yes   |
| 14        | 2      | 1087      | BFB         | 5         | Yes   |
| 14        | 10     | 7         | Mortgage    | 6         | Yes   |
| 14        | 11     | 17        | DDWW        | 7         | Yes   |
| 14        | 12     | 11        | IL          | 8         | Yes   |
| 14        | 13     | 5         | SAA         | 9         | Yes   |
| 14        | 14     | 9         | CD          | 10        | Yes   |

| 14        | 4      | 1088      | AAB         | NULL      | No    |
| 14        | 5      | 1089      | AAC         | NULL      | No    |
| 14        | 6      | 1090      | AAD         | NULL      | No    |

How should I need to change my query to get above output? please help me

Upvotes: 2

Views: 115

Answers (2)

Thom A
Thom A

Reputation: 95827

NULL has the lowest value, so you'll need to use a CASE to put NULL at the end, and then sort by SortValue:

ORDER BY CASE WHEN t.BranchServiceSortValue IS NULL THEN 1 ELSE 0 END,
         t.BranchServiceSortValue,
         t.serviceName;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Just add a key to the ORDER BY:

ORDER BY (CASE WHEN t.BranchServiceSortValue IS NOT NULL THEN 1 ELSE 2 END),
         t.BranchServiceSortValue, t.serviceName

The SQL standard provides the options NULLS FIRST and NULLS LAST for ORDER BY clauses. SQL Server does not (yet) implement these.

Upvotes: 2

Related Questions