Sorting by a NVARCHAR nullable column

Question

Post reply

Sorting by a NVARCHAR nullable column

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
May 23, 2016 at 7:19 am

#327250

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Not entirely safe, but it could work.

SELECT *

FROM #Work

ORDER BY ISNULL( Activitycode, NCHAR(4600));

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Here is another way you can do this.

select *

from #Work

order by Case when ActivityCode IS NULL then 1 else 0 end

, ActivityCode

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182937 More actions · Answer 3

Piling on

😎

IF OBJECT_ID(N'dbo.TBL_SAMPLE_ACTIVITY_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_ACTIVITY_DATA;

CREATE TABLE dbo.TBL_SAMPLE_ACTIVITY_DATA

(

Workid INT IDENTITY(1,1) NOT NULL

,Workname NVARCHAR(100) NOT NULL

,Activitycode NVARCHAR(100) NULL

);

INSERT INTO dbo.TBL_SAMPLE_ACTIVITY_DATA (Workname,Activitycode)

VALUES

(N'Drilling' ,N'S16-03779-01/016' )

,(N'planting' ,N'S16-03779-01/011' )

,(N'Field Work',NULL )

,(N'Lathe work',NULL )

,(N'planting' ,N'S16-03778-01/011' )

,(N'planting' ,N'S16-03777-01/011' )

;

SELECT

SAD.Workid

,SAD.Workname

,SAD.Activitycode

FROM dbo.TBL_SAMPLE_ACTIVITY_DATA SAD

ORDER BY ISNULL(SAD.Activitycode,N'ZZZZZZZZZZZZZZZZZZZZ')

,SAD.Workname;

;WITH BASE_DATA AS

(

SELECT

ROW_NUMBER() OVER

(

ORDER BY SAD.Activitycode

,SAD.Workname

) + CASE

WHEN SAD.Activitycode IS NOT NULL THEN 0

ELSE 2000000000

END AS SORT_RID

,SAD.Workid

,SAD.Workname

,SAD.Activitycode

FROM dbo.TBL_SAMPLE_ACTIVITY_DATA SAD

)

SELECT

BD.Workid

,BD.Workname

,BD.Activitycode

FROM BASE_DATA BD

ORDER BY BD.SORT_RID ASC;