Query to display NULL values last when sorting using ORDER BY in SQL Server

Hi makumbi,

In the ORDER BY clause use CASE statement.

Refer modified query.

SELECT TOP 100 PERCENT dbo.Destination37.Name,
    dbo.Destination37.ENG, dbo.Destination37.EG, dbo.Destination37.SCI,
    dbo.Destination37.SG, dbo.Destination37.SST, dbo.Destination37.GR,
    dbo.Destination37.MTC, dbo.Destination37.MG,
    dbo.Destination37. TS, dbo.Destination37.TG, dbo.Destination37.MU,
    dbo.Destination37.MGR, dbo.Destination37.AG,
    dbo.Destination37.AGRS, dbo.Destination37.AGR,
    dbo.Destination37.POS, dbo.Destination37.FS, dbo.Destination37.FGR,
    dbo.Destination37.Division AS DIV, dbo.Destination37.missed,
    dbo.P3P7.Stream, dbo.P3P7.Status, dbo.Destination37.tscore,
    dbo.Destination37.swa, dbo.Destination37.swgrade,
    dbo.Destination37.ire, dbo.Destination37.iregrade,
    dbo.Destination37.Computer, dbo.Destination37.compagg,
    dbo.Destination37.CompGrade, dbo.Destination37.Reading,
    dbo.Destination37.Ragg, dbo.Destination37.Rgrade
FROM dbo.Destination37 INNER JOIN
    dbo.P3P7 ON dbo.Destination37.Class = dbo.P3P7.Class AND
    dbo.Destination37.Name = dbo.P3P7.Name
GROUP BY dbo.Destination37.Name, dbo.Destination37.ENG,
    dbo.Destination37.EG, dbo.Destination37.SCI, dbo.Destination37.SG,
    dbo.Destination37.SST, dbo.Destination37.GR,
    dbo.Destination37.MTC, dbo.Destination37.MG,
    dbo.Destination37. TS, dbo.Destination37.TG, dbo.Destination37.MU,
    dbo.Destination37.MGR, dbo.Destination37.AG,
    dbo.Destination37.AGRS, dbo.Destination37.AGR,
    dbo.Destination37.POS, dbo.Destination37.FS, dbo.Destination37.FGR,
    dbo.Destination37.Division, dbo.Destination37.missed,
    dbo.P3P7.Stream, dbo.P3P7.Status, dbo.Destination37.tscore,
    dbo.Destination37.swa, dbo.Destination37.swgrade,
    dbo.Destination37.ire, dbo.Destination37.iregrade,
    dbo.Destination37.Computer, dbo.Destination37.compagg,
    dbo.Destination37.CompGrade, dbo.Destination37.Reading,
    dbo.Destination37.Ragg, dbo.Destination37.Rgrade
HAVING (dbo.P3P7.Status IS NULL)
ORDER BY CASE WHEN dbo.Destination37.POS IS NULL THEN 1 ELSE 0 END,dbo.Destination37.POS

Sample example.

DECLARE @test AS TABLE 
(
	No INT,Name VARCHAR(50),CS VARCHAR(10),ENG VARCHAR(10),EG VARCHAR(10),
	SST VARCHAR(10),GR VARCHAR(10),SCI VARCHAR(10),SG VARCHAR(10),MTC VARCHAR(10),MG VARCHAR(10),
	RE VARCHAR(10),RG VARCHAR(10),TS VARCHAR(10),TG VARCHAR(10),DIV VARCHAR(10),POS VARCHAR(10)
)
INSERT INTO @test VALUES(1,'AITA PADRIAG','P4RED',88,2,90,1,88,2,NULL,NULL,78,3,NULL,NULL,'U',NULL)		 
INSERT INTO @test VALUES(2,'AKELLO NOELLA MONICA','P4RED',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'U',NULL)	 
INSERT INTO @test VALUES(3,'SSENYANGE SHAYYARN','P4RED',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'U',NULL)	 
INSERT INTO @test VALUES(4,'TCHELU PASCALE','P4RED',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'U',NULL)	 
INSERT INTO @test VALUES(5,'TINO CLAIRE','P4RED',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'U',NULL)	 
INSERT INTO @test VALUES(6,'WEREMA SHIMEI HENRY','P4RED',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'U',NULL)	 
INSERT INTO @test VALUES(7,'ADELLE MIRELLA','P4RED',88,2,88,2,90,1,89,2,NULL,NULL,355,7,NULL,1)
INSERT INTO @test VALUES(8,'AINEBYONA SEAN','P4RED',99,1,78,3,67,4,90,1,NULL,NULL,334,9,NULL,2)
INSERT INTO @test VALUES(9,'ADUTIA ABRAHAM','P4RED',60,4,90,1,88,2,67,4,NULL,NULL,305,11,NULL,3)
INSERT INTO @test VALUES(10,'AFOYORWOTH JULIA','P4RED',90,1,55,5,77,3,78,3,NULL,NULL,300,12,NULL,4)
SELECT No,Name,ISNULL(CS,'')CS,ISNULL(ENG,'')ENG,ISNULL(EG,'')EG,ISNULL(SST,'')SST,ISNULL(GR,'')GR,ISNULL(SCI,'')SCI,ISNULL(SG,'')SG,
ISNULL(MTC,'')MTC,ISNULL(MG,'')MG,ISNULL(RE,'')RE,ISNULL(RG,'')RG,ISNULL(TS,'')TS,ISNULL(TG,'')TG,ISNULL(DIV,'')DIV,ISNULL(POS,'')POS
FROM @test
ORDER BY CASE WHEN POS IS NULL THEN 1 ELSE 0 END,POS

Output