CODEWITHSUNDEEP
sqlsql-server
Avinash
Avinash

Reputation: 45

Sql Server 2008: How to order column with null values to show up in the end

I am currently not able to sort the column to show up null values in the end

SELECT firstPart,secondPart  FROM Exhibit_table d, ExhibitType a WHERE d.case_id ='13-05'
AND d.ExhibitTypeId = TypeId AND d.ComplianceNo = '0' and active = 1 order by             CONVERT(INT, firstPart), secondPart

I have 2 columns firstpart and secondpart I need to sort it such a way that it shows in the following order

10
11
12 A
12 B
12 C
null null
null null

Any help is greatly appreciated

Upvotes: 0

Views: 395

Answers (1)

suff trek
suff trek

Reputation: 39767

You can add a third sorting condition:

SELECT firstPart,secondPart 
FROM Exhibit_table d, ExhibitType a 
WHERE d.case_id ='13-05' AND d.ExhibitTypeId = TypeId AND d.ComplianceNo = '0' and active = 1
ORDER BY CASE WHEN firstPart IS NULL AND secondPart IS NULL THEN 0 ELSE 1 END,
CONVERT(INT, firstPart), secondPart

This will introduce a calculated field with value of 0 when both fields are NULL and value of 1 otherwise - and sort by that field. You can adjust that condition as needed.

Upvotes: 2

Related Questions