December 7, 2020 at 8:12 pm
Sergiy wrote:jcelko212 32090 wrote:The ANSI/ISO standards specify an extra clause that goes after the order of the sort (ASC | DESC) which will let you decide the order of the nulls within that column (NULL FIRST | NULL LAST). Perhaps Microsoft will catch up in the future.
Don't see much of a point.
ORDER BY CASE WHEN Col IS NULL THEN 0 ELSE 1 END, Col
Switch 0 and 1 and you get your NULLS wherever you want them
The case statement needs a value, NULL is not a value, it is the absence of a value.
😎
Substituting non-value entries to "get the right results" is altering the data!
???
What value?
Where does that code substitute any value with a different one?
CASE statement does not take any value. It check for a condition to be fulfilled.
Checking for IS NULL is only one case of such validation.
Here is another one:
ORDER BY CASE Invoice.Status
WHEN "Overdue" THEN 0
WHEN "Open" THEN 1
WHEN ....
ELSE 99 END, ...
_____________
Code for TallyGenerator
December 8, 2020 at 6:50 pm
Just add your logic to a character column using 0 and 1 😉
😎
December 8, 2020 at 8:22 pm
Just add your logic to a character column using 0 and 1 😉
😎
i can add my logic to char, date time and bit columns, all at the same time:
ORDER BY CASE
WHEN Invoice.Status = ‘Overdue’ THEN 0
WHEN Invoice.DueDate > GETDATE() THEN 10
WHEN ACCOUNT.IsActive = 0 then 30
WHEN ....
ELSE 99 END, ...
Should add binary?
Sorting by the numbers in the end. What’s used for the conditions does not matter at all.
_____________
Code for TallyGenerator
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply