February 16, 2017 at 5:29 am
Hi all,
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.
Also, what is the purpose of repeating A.activitycode again after this expression (CASE WHEN A.activityCode IS NULL THEN 1 ELSE 0 END) ?
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,
SP.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)
Thanks a lot..
February 16, 2017 at 6:22 am
Quite simple really, it is pushing the NULL values to the back of the output rather than having them in front. The curious thing here is that although NULL is an unknown value, on SQL Server it is also regarded as the lowest value when it comes to the order of the values.
😎
February 16, 2017 at 8:23 am
What I understood is
If activitycode is null, go to last position , activitycode. That is the logic.
If activitycodes a1 to a10 are not null and activitycodes a11 to a20 are null, it will evaluate as below
ORDER BY a1,...,a10, Notes, sampleDescription, a11,...,a20
provided Notes and sampleDescription are also NOT NULL
Please correct me if I am wrong.
February 16, 2017 at 8:27 am
VSSGeorge - Thursday, February 16, 2017 8:23 AMWhat I understood is
If activitycode is null, go to last position , activitycode. That is the logic.If activitycodes a1 to a10 are not null and activitycodes a11 to a20 are null, it will evaluate as below
ORDER BY a1,...,a10, Notes, sampleDescription, a11,...,a20
provided Notes and sampleDescription are also NOT NULL
Please correct me if I am wrong.
For any given record, it will be sorted first on whether or not activitycode is null. As this will not break all ties, it next moves to the value of the activity code when it's not null, and if and only if that doesn't break all ties, does it move to the next expression in the order by clause. It will make the decision independently for each record, until it reaches the last expression, and if there is still a tie, then it does whatever is most convenient at that point in time in terms of choosing which record to place first. Does that help explain?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply