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

makumbi
 
on Jul 13, 2022 12:46 AM
243 Views

Please help i have used sqlserver to sort records below best on pos but the output is comming out like this yet i want the records at those starting with the first pos to be up

please help

No Name CS ENG EG SST GR SCI SG MTC MG RE RG TS TG DIV POS
1 AITA PADRIAG P4RED 88 2 90 1 88 2     78 3     U  
2 AKELLO NOELLA MONICA P4RED                         U  
3 SSENYANGE SHAYYARN P4RED                         U  
4 TCHELU PASCALE P4RED                         U  
5 TINO CLAIRE P4RED                         U  
6 WEREMA SHIMEI HENRY P4RED                         U  
7 ADELLE MIRELLA P4RED 88 2 88 2 90 1 89 2     355 7   1
8 AINEBYONA SEAN P4RED 99 1 78 3 67 4 90 1     334 9   2
9 ADUTIA ABRAHAM P4RED 60 4 90 1 88 2 67 4     305 11   3
10 AFOYORWOTH JULIA P4RED 90 1 55 5 77 3 78 3     300 12   4
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 dbo.Destination37.POS
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 13, 2022 01:08 AM

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

No Name CS ENG EG SST GR SCI SG MTC MG RE RG TS TG DIV POS
7 ADELLE MIRELLA P4RED 88 2 88 2 90 1 89 2     355 7   1
8 AINEBYONA SEAN P4RED 99 1 78 3 67 4 90 1     334 9   2
9 ADUTIA ABRAHAM P4RED 60 4 90 1 88 2 67 4     305 11   3
10 AFOYORWOTH JULIA P4RED 90 1 55 5 77 3 78 3     300 12   4
1 AITA PADRIAG P4RED 88 2 90 1 88 2     78 3     U  
2 AKELLO NOELLA MONICA P4RED                         U  
3 SSENYANGE SHAYYARN P4RED                         U  
4 TCHELU PASCALE P4RED                         U  
5 TINO CLAIRE P4RED                         U  
6 WEREMA SHIMEI HENRY P4RED                         U