Ordering with NULL
-
Comments posted to this topic are about the item Ordering with NULL
-
At first, I thought this was more of a "did you actually read the code question" than anything else.  I no longer think that because at least one person said the NULL row would be eliminated.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I could have sworn that where the NULL would be in the sorting depended on the collation.
-
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.
Please post DDL and follow ANSI/ISO standards when asking for help.
- 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.
Heh... but ordering in a database shouldn't matter, right Joe!? 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
JC makes a good point, the ANSI/ISO has the extra clause of (NULL FIRST | NULL LAST), unfortunately, omitted by many vendors including MS.
😎
In SQL Server, NULL FIRST is implied, NULL LAST means using ISNULL and the highest value as a replacement, bit of a bonkers approach.
-
Steve, you're not the chap I thought you were.
Where's the Fuller's Golden Honeydew, and then where was the Guiness. (Or, have you drunk them all?).
Kenneth Spencer
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer - kaspencer wrote:
Steve, you're not the chap I thought you were.
Where's the Fuller's Golden Honeydew, and then where was the Guiness. (Or, have you drunk them all?).
Kenneth Spencer
Why go for the Golden Honeydew when you can have ESB
😎
Have more than a handful and ESB becomes Extra SQL Blunder 😉
-
I grabbed the first few names I thought of from a visit to a local restaurant. I have had these, and I like some of them. I tend to be an Amber or Brown ale guy, but a nice Guinness while in Dublin is always welcome. I have tried the Golden Honeydew, but I'll add it to a list if I ever get back over to the UK and they let me in.
Here in Denver, we have a lot of local craft brews. This is one of my favorites, https://www.105westbrewing.com, though unfortunately I can't keep Deez Nuts in stock at the house. My daughter and son will drink them up.
-
The password for getting into the UK is SQLServerCentral!
My kids (they are both 40-something now) have to buy their own!
Best wishes,
Ken.
You never know: reading my book: "All about your computer" might just tell you something you never knew!
lulu.com/kaspencer - 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
_____________
Code for TallyGenerator -
The case expression returns a value of a particular data type. In your example, you're sorting on a numeric, without regard to what the original column was. This would seem to have an awful lot of overhead as well as possible incorrect values and a different correlation if the original column was a character data type.. But just from an aesthetic viewpoint, this is a kludge to get around the lack of a standard feature.
Please post DDL and follow ANSI/ISO standards when asking for help.
- 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!
- jcelko212 32090 wrote:
The case expression returns a value of a particular data type. In your example, you're sorting on a numeric, without regard to what the original column was. This would seem to have an awful lot of overhead as well as possible incorrect values and a different correlation if the original column was a character data type.
what???
Are you sure about it?
Can you elaborate on it please? I cannot see your case.
But just from an aesthetic viewpoint, this is a kludge to get around the lack of a standard feature.
I never heard of any art excibition showing new (or traditional) trends in SQL coding. I don't think people would appreciate the aestetics of it anyway.
As for myself - I'm trying my best to make the code working with no errors, and with no performance degradation, so neither me nor anyone else would need to open it again, and no aesthetic confrontations would come from it.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply