SQLTeam.com | Weblogs | Forums

Need help to understand the query

tsql
sql2008r2

#1

Please help me to understand the logic written in the ORDER BY clause of the SQL query below. what it means by writing the CASE condition.

SELECT        ROW_NUMBER() OVER(Order by internalSampleCode) as RowC, internalSampleCode, 
CONVERT(NVARCHAR(1000), SP.notes) AS Treatment, 
CONVERT(NVARCHAR(1000), SP.sampleDescription) AS Matrix,
CONVERT(NVARCHAR(1000), SP.remarks) AS SamplingInstructions, 
samplePartnerCode AS eSMSampleCode,
A.activityCode AS Activity
FROM  SamplesPartners AS SP
LEFT JOIN ActivitiesSamplesPartners ASP ON SP.samplePartnerIncId = ASP.samplePartnerIncId
	AND SP.samplePartnerSqlId = ASP.samplePartnerSqlId
	AND SP.isDeleted = 0
	AND ASP.isDeleted = 0
LEFT JOIN Activities A ON A.activityIncId = ASP.activityIncId
	AND A.activitySqlId = ASP.activitySqlId
	AND A.isDeleted = 0
WHERE SP.samplePartnerCode  IN (@specimencode)
      ORDER BY (CASE WHEN A.activityCode IS NULL THEN 1 ELSE 0 END), A.activityCode,
	           (CASE WHEN CONVERT(NVARCHAR(1000), SP.notes) IS NULL THEN 1 ELSE 0 END), CONVERT(NVARCHAR(1000), SP.notes),
	           (CASE WHEN CONVERT(NVARCHAR(1000), SP.sampleDescription) IS NULL THEN 1 ELSE 0 END), CONVERT(NVARCHAR(1000), SP.sampleDescription)

#2
(CASE WHEN A.activityCode IS NULL THEN 1 ELSE 0 END), 

If the activityCode is NULL the sort that second,sort anything NOT NULL first

Then sort by activityCode

CASE WHEN CONVERT(NVARCHAR(1000), SP.notes) IS NULL THEN 1 ELSE 0 END

Convert the [notes] to NVarchar(1000) - not sure why this is necessary? Probably a slack why of truncating to first 1000 characters, and then if [notes] is NULL sort it second, if NOT NULL sort if first

CONVERT(NVARCHAR(1000), SP.notes)

then sort by Notes (having converted to NVarchar(1000) - not sure what the point of that is

CASE WHEN CONVERT(NVARCHAR(1000), SP.sampleDescription) IS NULL THEN 1 ELSE 0 END

Similarly if [sampleDescription] is NULL sort it second, if NOT NULL sort if first

CONVERT(NVARCHAR(1000), SP.sampleDescription)

then sort by [sampleDescription]


#3

Thanks Kristen for the explanation.

From the reply, I can understand like if activitycode is null, it will be sorted by Notes and sampleDescription.

What is the purpose of repeating A.activitycode again after this expression (CASE WHEN A.activityCode IS NULL THEN 1 ELSE 0 END) ?

It is already understood from the CASE expression like if activitycode is null, it will be ordered by Notes or sampleDescription, provided they are NOT NULL.

Please explain. Thanks


#4

What this is doing is forcing NULL values at each level to sort to the bottom of that group. Sorting values would be:

0, AC, 0, Note, 0, Description
1, Null, 0, Note, 0, Description
1, Null, 1, Null, 0, Description

For each section - the NULL values will be forced to sort to the bottom of that section.