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)
(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
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.