1

I have a student table with a test date column and I want to create a query that will order by date from the oldest first to new and that all the columns that have null in it will be the last

what I need

01/01/2020
15/02/2020
null
null

what i get

null
null
01/01/2020
15/02/2020

is it pussible do it without invoke 2 queries or add an additional column and use then order..

tnx

Leon Barkan
  • 2,676
  • 2
  • 19
  • 43

3 Answers3

1

You could order by a CASE expression:

SELECT *
FROM yourTable
ORDER BY CASE WHEN date_col IS NOT NULL THEN 0 ELSE 1 END, date_col;

This approach used a two level sort which puts all non null date records first, followed by null ones last. Within each of those two groups, we sort ascending by date.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • The resulting values of a case expression must be all the same type, so SQL will convert the 1 or zero to a date, which may give you an unexpected result. – Sean Oct 27 '20 at 07:43
  • @Sean ... um the only possible outputs from the `CASE` expression I used are 0 or 1. Last time I checked they were both integers, so ... – Tim Biegeleisen Oct 27 '20 at 07:44
0

For sql server :

CREATE TABLE Test(
date datetime NULL
)


insert into Test (date)
values('2020-01-01'),
('2020-02-15'),
(NULL),
(NULL)


select * from Test
ORDER BY IIF(date IS NULL, 1, 0), date;

See the link : https://rextester.com/CXK25023

Rasel Ahmed
  • 86
  • 4
-1

Try replacing the null value with something more meaningful in your order by. So

isnull(column1, '2100-01-01')

Sean
  • 1,416
  • 19
  • 51