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](https://stackoverflow.com/questions/5886857/sql-server-order-by-date-and-nulls-last) — GuidoG, Oct 02 '18 at 07:45
Same column name as table name is asking for trouble. Use plural names for tables (several _occupations_ are stored in the table.) — jarlh, Oct 02 '18 at 07:58
You need to join on rowid instead. (Or whatever SQL Server has.) — jarlh, Oct 02 '18 at 08:19
Would be really nice if SQL Server supported Standard SQL's `nulls first/last` option like Oracle, DB2, Postgres, etc... — dnoeth, Oct 02 '18 at 08:41

score 4 · Answer 1 · answered Oct 02 '18 at 07:47

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 02 '18 at 07:47

sticky bit

36,626
12
31
42

2

Does using a case performs better than using isnull() in the order by ? – GuidoG Oct 02 '18 at 07:48
2

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. – sticky bit Oct 02 '18 at 07:52

score 1 · Answer 2 · answered Oct 02 '18 at 07:47

1

you can use

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

answered Oct 02 '18 at 07:47

Milad Aghamohammadi

1,866
1
16
29

score 1 · Answer 3 · answered Oct 02 '18 at 07:59

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

Order by Value before NULL SQLSERVER

3 Answers3