CODEWITHSUNDEEP
sqlsql-server
ayman
ayman

Reputation: 29

Order by a specific value first then show all rest in order

The below query returns list of areas i want to show the null value which is '--- All ----' then the rest of the values.

So out out will be

AreaID  AreaName
Null    '--- All ----'
1       area1
2       area2

etc..

Query 

SELECT
dbo.Areas.AreaID,
dbo.Areas.AreaName
FROM dbo.Areas
UNION
SELECT
NULL,
'--- All ----'
ORDER BY dbo.Areas.AreaName

Upvotes: 0

Views: 667

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

One option is to use a CASE expression to place your NULL record first in the result set. You can also add a second ordering condition to sort on the area name after this first condition has been applied.

SELECT
    dbo.Areas.AreaID,
    dbo.Areas.AreaName
FROM dbo.Areas
UNION
SELECT
    NULL,
    '--- All ----'
ORDER BY CASE WHEN dbo.Areas.AreaID IS NULL THEN 0 ELSE 1 END,
         dbo.Areas.AreaName

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT AreaID,
       AreaName
FROM (
   SELECT dbo.Areas.AreaID,
          dbo.Areas.AreaName
   FROM dbo.Areas

   UNION

   SELECT NULL,
          '--- All ----'
) AS t
ORDER BY CASE 
            WHEN AreaID IS NULL THEN 1
            ELSE 2
         END,
         AreaName

Note: Using a nested derived table is not required as it would have sufficed to use ORDER BY like you did in your query. I think though it adds to the readability of the query.

Upvotes: 2

Related Questions