SQL - Sorting NULL values to bottom?

McGintyMcGoo

Greetings

My table contains a "Date Required" field that I allow the user to sort on. However, as this field is not compulsory, it can contain nulls! The problem is that the customer does not want the null values to appear first in the table when it is sorted by "Date Required" ASC. (MSSQL2K)

The obvious answer would be to force the user to always enter a date. If they did not know it, it could default to some future date e.g. 31/12/2020. However, the customer wants the option to leave the field blank (null).

Is there an obvious way of resolving this issue?

PhilH

Hi there.

First up - I tested by DB and found that null dates sorted to the bottom, not the top, when doing an ORDER BY ASC. I'm using PostgreSQL, not MS SQL.

Anyway, if this is not a setting you have access to (or want to change), you could use the COALESCE function which is, I believe, standard SQL.

I have a table in which the 'created' data is nullable. The following select statement gets the contents of the table with the rows containing null dates sorted as though they were a very distant date.

select name, created, coalesce(created, '2999-01-01') as ds from testtable order by ds asc;

The COALESCE function returns the first of its arguments that is not null.

PHiL

Kernel32

So if you don't want it to appear first then I assume you want them to appear last? You could use IsNull like this using a date way in the future

Select field1, field2, datefield from table1 order by isnull(datefield, '01/01/3000') ASC

A more elegant solution but probably slightly slower over large resultsets is this..

select field1, field2, datefield ,
DateFieldSort = case isnull(datefield,'')
When '' Then 1
Else 0
End
from table1 order by DateFieldSort Asc, adrcity Asc

The idea is to create a column in the resultset that contains 1 or 0, 1 means null, 0 means not null. Then do the primary sort on date followed by a secondary sort on the datefield and bob's your uncle. This type of solution is more elegant because there is no hard coded date.

If you are doing this in a stored procedure though, which I sincerely hope you are because dynamic SQL is evil then you could take the first piece of SQL and replace the hardcoded date with a variable containing the max date + 1 for that field in the table.

bonkey

Originally posted by Kernel32

A more elegant solution but probably slightly slower over large resultsets is this..

select field1, field2, datefield ,
DateFieldSort = case isnull(datefield,'')
	When '' Then 1
	Else 0
End
from table1 order by DateFieldSort  Asc, adrcity Asc

If performance is an issue, then you can simplify this down by removing one of the functions :

select field1, field2, datefield , 
case  When datefield is null Then 0 
      Else 1 
end as datefieldsort
from table1 order by DateFieldSort  Asc, datefield asc, adrcity Asc

Having an isnull, as Kernel32 had, is pointless if you're gonna put the result into a CASE statement. Just CASE it using the alternate version of the CASE syntax

dynamic SQL is evil

No, it isn't.

it is only evil when you misuse it, like using it without using bound parameters.

Dynamic SQL is oftentimes far superior to Stored Procedures for certain types of problem.

jc

Merrion

Use the IsNull keyword i.e.
Sybase example...

select field1, field2, datefield 
from table1 
order by isnull(datefield , '2079-06-06') Asc, adrcity Asc

Kernel32

yeah I knew there was multiple ways to do the case but I was too lazy to open the help file and look it up.

I will agree on occasion dynamic SQL can be a solution to a problem but even then I will put it in a stored proc. The reason isn't always performance, on a large project it is for maintenance reasons, keep SQL where is should be and thats in the database and not scattered around the other tiers. Thats just my opinion and I have never found any time when not having it in stored procs is superior.

bonkey

Originally posted by Kernel32
I will agree on occasion dynamic SQL can be a solution to a problem but even then I will put it in a stored proc.

Oof. That I wouldn't do, because its far harder to ensure that you haven't been sent any "abusive" strings which will cause funny things to happen. At the least, you have to go running replace searches on every string to ensure you haven't been sent up a string-terminator in it...or worse.

But hey...each to their own. I usually use stored procs as much as possible, but I just think that well-managed dynamic SQL can have a lot going for it in certain situations when done correctly.

keep SQL where is should be and thats in the database and not scattered around the other tiers.

I don't have it scattered around anything. I have it in a QueryFactory, which is typically implemented as a linked DLL so I can easily replace it seperately to the application when anything changes.

jc