Ordering NULL results differently in SQL Server?

Question

I have a query that currently brings up data like this:

ID | Name | Arrival  | Departure
-----------------------------------
1  | John | NULL     |  2:30:00
2  | John | NULL     | 11:00:00
3  | John | NULL     | 14:00:00
4  | John | 10:30:00 | 11:00:00
5  | John | 12:00:00 | 13:00:00

This is when I do: order by Name, Arrival, Departure.

However, what I would like instead is the following:

1. If the Arrival entry is NULL, then rows should be ordered by the Departure column.
2. If the Arrival entry is NULL AND there are multiple rows with the same Departure data, then it should be ordered after the other rows that have an Arrival entry.

Result:

ID | Name | Arrival  | Departure
-----------------------------------
1  | John | NULL     |  2:30:00
4  | John | 10:30:00 | 11:00:00
2  | John | NULL     | 11:00:00
5  | John | 12:00:00 | 13:00:00
3  | John | NULL     | 14:00:00

Edit: For those who thought this is a duplicate question - originally the questions are not the same due to the second condition. However, because of the way my data is structured (Arrival times are always before Departure times), the same answer is applicable.

Edit 2: Departure can be NULL as well. When Departure is null, the Arrival entry is non-null. If this is the case, it should be ordered by Arrival. If two rows both have the same Arrival entry, then NULLs should be BEFORE non-nulls. Note this is opposite to NULL Arrival entries (condition 2 above), where NULLs are after non-nulls.

Answer 1

Try it with

DECLARE @tbl TABLE(ID INT,Name VARCHAR(100),Arrival TIME,Departure TIME);
INSERT INTO @tbl VALUES
 (1,'John',NULL,'2:30:00')
,(2,'John',NULL,'11:00:00')
,(3,'John',NULL,'14:00:00')
,(4,'John','10:30:00','11:00:00')
,(5,'John','12:00:00','13:00:00');

SELECT * FROM @tbl
ORDER BY Name,CASE WHEN Arrival IS NULL THEN Departure ELSE Arrival END

The result

1   John    NULL                02:30:00.0000000
4   John    10:30:00.0000000    11:00:00.0000000
2   John    NULL                11:00:00.0000000
5   John    12:00:00.0000000    13:00:00.0000000
3   John    NULL                14:00:00.0000000

You might think about ISNULL(), but you should read about "sargable", especially if you deal with many rows and indexes...

Answer 2

How about simple ORDER without conditional expressions and functions.

If you want to order by Departure first, well, order by it. If you want to order by Arrival next, but want NULL last, use DESC.

This will work correctly even if Arrival is the same as Departure, for example, if Arrival was 11:00:00 in row 4 instead of 10:30:00.

I'd like to thank @Shnugo for providing the script with the test data.

DECLARE @tbl TABLE(ID INT,Name VARCHAR(100),Arrival TIME,Departure TIME);
INSERT INTO @tbl VALUES
 (1,'John',NULL,'2:30:00')
,(2,'John',NULL,'11:00:00')
,(3,'John',NULL,'14:00:00')
,(4,'John','10:30:00','11:00:00')
,(5,'John','12:00:00','13:00:00');

SELECT * 
FROM @tbl
ORDER BY
    Name
    ,Departure
    ,Arrival DESC;

Result

+----+------+------------------+------------------+
| ID | Name |     Arrival      |    Departure     |
+----+------+------------------+------------------+
|  1 | John | NULL             | 02:30:00.0000000 |
|  4 | John | 10:30:00.0000000 | 11:00:00.0000000 |
|  2 | John | NULL             | 11:00:00.0000000 |
|  5 | John | 12:00:00.0000000 | 13:00:00.0000000 |
|  3 | John | NULL             | 14:00:00.0000000 |
+----+------+------------------+------------------+

Though... It is not clear if it is possible to have more than one row with non-null Arrival and exactly the same Departure... What ordering you'd like in this case?

Answer 3

You can do case statements in an order by clause I believe.

ISNULL would be my preferred way to go though because it's specifically built to work with nulls and it's smaller.

ORDER BY Name, ISNULL(Arival, Departure)

Answer 4

In order to avoid using conditionals in WHERE clause you can persist your ordering criteria by using a computed column. Also, using TIME type for the Arrival and Departure is more efficient (space, comparisons etc.):

create table Data 
(
    ID INT NOT NULL,
    Name VARCHAR(100) NOT NULL,
    Arrival TIME NULL,
    Departure TIME NULL,
    ActualTime AS ISNULL(Arrival, Departure) PERSISTED
)
GO

INSERT INTO Data (ID, Name, Arrival, Departure)
VALUES
 (1,'John',NULL,'2:30:00')
,(2,'John',NULL,'11:00:00')
,(3,'John',NULL,'14:00:00')
,(4,'John','10:30:00','11:00:00')
,(5,'John','12:00:00','13:00:00');
GO

select * from Data
order by ActualTime

Based on other queries, you can also have an index using ActualTime as a key.

Answer 5

To get null to come last I think you can just add

ORDER BY Name 
       , ISNULL(Arival, dateadd(ss, 1, Departure)) 
       , Departure 

but if you have arrival that go into the next departure with nulls then you are going to get some maybe not desired results
you would need to state exactly how you want to deal with that

what if you had

6  | John | 12:30:00 | 11:00:00 

do you really want that after

5  | John | 12:00:00 | 13:00:00

it would be a lot easier to just sort on departure first

declare @bigdate datetime = cast('2099-01-01' as datetime);
ORDER BY Name  
       , Departure 
       , ISNULL(Arival, @bigdate);