April 6, 2016 at 11:14 pm
Comments posted to this topic are about the item ORDER BY ASC and NULLs last
April 6, 2016 at 11:16 pm
This was removed by the editor as SPAM
April 7, 2016 at 1:43 am
Tricky.
April 7, 2016 at 1:53 am
Stewart "Arturius" Campbell (4/6/2016)
Interesting, never thought of doing it that wayNice question, thanks Horia
+1
April 7, 2016 at 2:25 am
It also works using Order by val desc which is what I selected so I got an error on the correct answer
April 7, 2016 at 2:29 am
g.maxfield (4/7/2016)
It also works using Order by val desc which is what I selected so I got an error on the correct answer
That is not correct what you say. The ORDER BY DESC will return 1, 0, -1, NULL, and not -1, 0, 1, NULL.
April 7, 2016 at 4:09 am
OK Noted. Thanks 😎
April 7, 2016 at 5:30 am
Carlo Romagnano (4/7/2016)
Stewart "Arturius" Campbell (4/6/2016)
Interesting, never thought of doing it that wayNice question, thanks Horia
+1
+1 as well. Very cool.
April 7, 2016 at 5:42 am
Thanks Horia, interesting question and good explanation. It may be useful when dealing with NULLs.
April 7, 2016 at 5:50 am
Stewart "Arturius" Campbell (4/6/2016)
Interesting, never thought of doing it that wayNice question, thanks Horia
Nor should you. In production code, I would always write out the explicit intent instead of relying on such a trick.
ORDER BY CASE WHEN val IS NULL THEN 2 ELSE 1 END, -- Ensure that NULL sorts last
val -- Within non-NULL, order by ascending value
April 7, 2016 at 6:17 am
The correct answer works with numeric column, but it doesn't with other types.
April 7, 2016 at 7:07 am
Hugo Kornelis (4/7/2016)
Stewart "Arturius" Campbell (4/6/2016)
Interesting, never thought of doing it that wayNice question, thanks Horia
Nor should you. In production code, I would always write out the explicit intent instead of relying on such a trick.
ORDER BY CASE WHEN val IS NULL THEN 2 ELSE 1 END, -- Ensure that NULL sorts lastval -- Within non-NULL, order by ascending value
I'm not sure I'd call it a trick -- it's using a documented aspect of the SQL-92 standard (see my post on an early question at http://www.sqlservercentral.com/Forums/FindPost1768805.aspx
Having said that, it's not a commonly-known aspect, so at least commenting how it works would seem in order.
I'm not sure whether a CASE with a NULL test on every row would be more or less performant than a *-1 on every row, but that's something to consider as well.
April 7, 2016 at 7:27 am
Fun question, thank you.
Be still, and know that I am God - Psalm 46:10
April 7, 2016 at 8:02 am
Nice trick. I'll keep that in mind.
April 7, 2016 at 8:35 am
Hugo, is there really a difference? In both cases its clearly something unusual being done and I'd argue in both cases a "why" comment would be appropriate.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply