SQLTeam.com | Weblogs | Forums

Ascending order sorting by a NVARCHAR nullable column

sql2008r2

#1

I have a table Activity which has a column 'ActivityCode' which is having data type NVARCHAR(100).
The data in the column will be in this format
'S16-03779-01/016'

Another table called 'Work' has column WorkId int , Workname varchar(100).

Workid Workname Activitycode

02333 Drilling S16-03779-01/016
09782 planting S16-03779-01/011
03788 Field Work NULL
02881 Lathe work NULL

For a particular workid and workname, the activity can be available or NULL.

how to make null values come last when sorting ascending by 'ActivityCode' in a select statement as it is NVARCHAR column.


#2

You can add a clause like this:

ORDER BY ISNULL(ActivityCode ,'ZZZZZZZZZ') ASC

#3
ORDER BY
	CASE WHEN ActivityCode IS NULL THEN 1 ELSE 0 END,
	ActivityCode;

#4

Thanks JamesK, it worked.

I know this will work for integer data type, but had doubt as it is NVARCHAR.