April 7, 2016 at 8:55 am
Never thought of doing it this way. Thanks!
April 7, 2016 at 9:26 am
Andy Warren (4/7/2016)
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.
I personally like to be explicit when I create an ORDER BY that treats a selection of the data different. The CASE in the ORDER BY makes that very clear.
It is a bit more typing, of course, but I would otherwise add a comment to the ORDER BY to clarify why I use the prefix minus operator and the descending order to get an ascending order with a twist.
It os of course very much a matter of personal preference. 😉
April 7, 2016 at 9:30 am
Andy Warren (4/7/2016)
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.
Yes, there is really a difference.
sknox (4/7/2016)
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.
No, Hugo is right, it has to be considered a trick rather than a valid technique.
If the column had been declared with a numeric type other than int or bigint or smallint it would have been a reliable technique, not a trick, but as the declaration given uses int, here we have an unreliable trick.
It will usually work, but what happens if one of the integer values is -2147483648 ? Applying minus to that will cause arithmetic overflow, so it's unsafe to use with ints. It's equally unsafe with bigints (try it with the value -9223372036854775808). It works for smallints because (contrary to BOL, so presumably this is a bug in SQL Server) unary minus on a smallint promotes the result to int. It works for tinyint (unary minus is documented as promoting tinyint to smallint; unary minus obviously can't deliver tinyint because because there are no negative tinyints). It works for all the non-integer numerics (because their negative values are handled with a separate sign bit, not using twos complement so that the sign bit can be part of the absolute magnitude).
Tom
April 7, 2016 at 10:05 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
+1
Production code shouldn't make you scratch your head to figure out how it's supposed to function. It's hard enough trying to figure out why code doesn't work as intended, without the added challenge of trying to determine the coder's original goal.
Nice QotD, but I wouldn't implement this in production code either. CASE statements ("expressions"? I can never remember which is semantically correct) are largely self-documenting, and the addition of a quick comment nails down the intent firmly.
Rich
April 8, 2016 at 7:00 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
Hugo, thanks for sharing this - it's elegant and works regardless of what your other sort operators are.
April 11, 2016 at 3:50 am
Nice and interesting question for which I have chosen the bad proposal. I need to study this topic. I will do it during this afternoon. Anyway , an useful question so thanks Horia...
April 11, 2016 at 6:29 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2016 at 4:43 pm
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.
Agreed. Tricks are cute and fun but should be used sparingly. The next programmer that looks at it may not understand your trick. I consistently use the case statement as you described.
August 2, 2016 at 2:58 am
It will also work if you write your query like below
SELECT val FROM @table ORDER BY val desc
August 2, 2016 at 3:31 am
gauravsrmsin (8/2/2016)
It will also work if you write your query like belowSELECT val FROM @table ORDER BY val desc
How exactly would that sort in ascending order?
August 3, 2016 at 1:04 am
It will work as per required result and give the following result.
1
0
-1
NULL
August 3, 2016 at 1:26 am
gauravsrmsin (8/3/2016)
It will work as per required result and give the following result.1
0
-1
NULL
The problem posed in the question is this (emphasis mine):
"I need to return the data sorted ascending and with NULLs at the end of the results."
Your result set is descending and with NULLs at the end of the results.
August 4, 2016 at 7:08 am
Sorry...
that's mean.. i didn't attention on the question
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply