Order by Value before NULL SQLSERVER

Question

I'm trying to order every column by value in alphabetical order and then by followed by NULL. However, it is not working. I tried to place order by at each query before fully joined , but it doesn't allow so what could be some possible ways to achieve the goal?

Select t1.Name AS Doctor, t2.Name AS Professor, t3.Name AS Singer,t4.Name AS Actor
FROM (
      SELECT Name 
      FROM Occupation
      Where Occupation ='Doctor' 
                                )  t1 
FULL JOIN
    (
     SELECT Name 
     FROM Occupation
     Where Occupation ='Professor'
                                ) t2
on t1.Name=t2.Name
FULL JOIN
  (
     SELECT Name 
     FROM Occupation
     Where Occupation ='Singer'
                                ) t3
on t1.Name=t3.Name
FULL JOIN
   (
     SELECT Name 
     FROM Occupation
     Where Occupation ='Actor'
                               ) t4
on t1.Name=t4.Name
ORDER BY Doctor ASC, Professor ASC, Singer ASC, Actor ASC

Wrong Result:

Doctor    Professor    Singer  Actor
-------------------------------------------
  NULL     NULL         NULL    Jane
  NULL     NULL         NULL    Julia
  NULL     NULL         NULL    Maria
  NULL     NULL         Meera   NULL
  NULL     NULL         Priya   NULL
  NULL     Ashley       NULL    NULL
  NULL     Christeen    NULL    NULL
  NULL     Ketty        NULL    NULL
  Jenny    NULL         NULL    NULL
  Samantha NULL         NULL    NULL

Expected Result:

Doctor    Professor    Singer  Actor
-------------------------------------------
  Jenny    Ashley       Meera   Jane
  Samantha Christeen    Priya   Julia
  NULL     Ketty        NULL    Maria
  NULL     NULL         NULL    NULL
  NULL     NULL         NULL    NULL
  NULL     NULL         NULL    NULL
  NULL     NULL         NULL    NULL
  NULL     NULL         NULL    NULL
  NULL     NULL         NULL    NULL
  NULL     NULL         NULL    NULL

Possible duplicate of SQL Server ORDER BY date and nulls last — GuidoG
– GuidoG, Commented Oct 2, 2018 at 7:45
Same column name as table name is asking for trouble. Use plural names for tables (several occupations are stored in the table.) — jarlh
– jarlh, Commented Oct 2, 2018 at 7:58
Would be really nice if SQL Server supported Standard SQL's nulls first/last option like Oracle, DB2, Postgres, etc... — dnoeth
– dnoeth, Commented Oct 2, 2018 at 8:41

sticky bit · Accepted Answer · 2018-10-02 07:47:34Z

4

Use a CASE that returns a higher value if a value is null than if it's not null.

...
ORDER BY CASE
           WHEN doctor IS NULL THEN
             1
           ELSE
             0
         END,
         doctor,
         CASE
           WHEN professor IS NULL THEN
             1
           ELSE
             0
         END,
         professor,
         CASE
           WHEN singer IS NULL THEN
             1
           ELSE
             0
         END,
         singer,
         CASE
           WHEN actor IS NULL THEN
             1
           ELSE
             0
         END,
         actor

answered Oct 2, 2018 at 7:47

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GuidoG Over a year ago

Does using a case performs better than using isnull() in the order by ?

sticky bit Over a year ago

If there's a difference I expect it to be neglectable. The point more valuable in my option is, that with the CASE it works for arbitrary values whereas with an isnull() one has to have a value "above" all others, that may clash with future versions, if one extends the data set and forgets to alter the query.

Milad Aghamohammadi · Accepted Answer · 2018-10-02 07:47:00Z

1

you can use

ORDER BY isnull(Doctor, 'zzzzzzzzzzzzzzz') ASC
       , isnull(Professor, 'zzzzzzzzzzzzzzz') ASC
       , isnull(Singer, 'zzzzzzzzzzzzzzz') ASC
       , isnull(Actor, 'zzzzzzzzzzzzzzz') ASC

answered Oct 2, 2018 at 7:47

Milad Aghamohammadi

1,9961 gold badge21 silver badges31 bronze badges

Comments

lukaszberwid · Accepted Answer · 2018-10-02 07:59:12Z

1

You can also try

order by case when Column is not null then Column else Replicate(char(255),Len(255)) end

where you replace 255 in Len(255) with the accucual length of the column.

I think this is the most robust solution since if you have all ascii characters in database values of 'z' is 122, so there is some possiblity user may use for eg. '(' and this will mess up your sort.

But if you don't have such corner cases anwser by Milad Aghamohammadi is also ok and much simpler

answered Oct 2, 2018 at 7:59

lukaszberwid

1,1879 silver badges21 bronze badges

Collectives™ on Stack Overflow

Order by Value before NULL SQLSERVER

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related