December 29, 2010 at 10:49 am
If I'm asked to sort a result set in "ascending order based on the numeric value of a string," does that mean based on the value of the first numer in the string (if there is one) and then alphabetically?
Mike Scalise, PMP
https://www.michaelscalise.com
December 29, 2010 at 10:57 am
SQL Challenge 46, right?
The answer is, only the person who wrote it knows what they meant, and they haven't replied yet. So, nobody knows.
Thus far, the guesses on it on their forums are pretty amusing. One guy suggested A = 41, B = 42 (not sure where he got those numbers from), and since 4121 is higher than 42, AB would come after B. This system would, of course, overload SQL Server if the string actually is in the higher range for varchar(max), which the challenge calls for.
So, it's either a nonsense requirement, or it means something other than what it seems to say.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 10:59 am
Is this what you mean by sorting a string in numeric order?
1
1.1
1.3.4
1.22.0
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 29, 2010 at 11:32 am
GSquared (12/29/2010)
One guy suggested A = 41, B = 42 (not sure where he got those numbers from)
Probably hex values. 0x41 = 65, the ascii-value of a capital 'A', 0x42 = 66, a 'B', etc.
December 29, 2010 at 11:35 am
Peter Brinkhaus (12/29/2010)
GSquared (12/29/2010)
One guy suggested A = 41, B = 42 (not sure where he got those numbers from)Probably hex values. 0x41 = 65, the ascii-value of a capital 'A', 0x42 = 66, a 'B', etc.
I thought of that too. But even if that's true, does that mean "Order By cast(column as varbinary(max))", or something else? Assuming that's even semi-official.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 29, 2010 at 11:52 am
GSquared (12/29/2010)
Peter Brinkhaus (12/29/2010)
GSquared (12/29/2010)
One guy suggested A = 41, B = 42 (not sure where he got those numbers from)Probably hex values. 0x41 = 65, the ascii-value of a capital 'A', 0x42 = 66, a 'B', etc.
I thought of that too. But even if that's true, does that mean "Order By cast(column as varbinary(max))", or something else? Assuming that's even semi-official.
The question has been answered in the related forum over at beyondrelational. 😛
The guy who suggested A=41 most probably is the one responsible for the challenge (I'm not sure if dishdy will be the forum master for this one...)
But yes, 41 is the hex value for 'A'.
To sort simply by varbinary(max) won't help since it doesn't recognize the string length. As per the example provided over there: the string 'AB' would be after 'B' since the number 42 is less than the number 4142... (= strong hint 🙂 ). 'nuff said from my side...
December 29, 2010 at 11:53 am
GSquared (12/29/2010)
SQL Challenge 46, right?The answer is, only the person who wrote it knows what they meant, and they haven't replied yet. So, nobody knows.
Thus far, the guesses on it on their forums are pretty amusing. One guy suggested A = 41, B = 42 (not sure where he got those numbers from), and since 4121 is higher than 42, AB would come after B. This system would, of course, overload SQL Server if the string actually is in the higher range for varchar(max), which the challenge calls for.
So, it's either a nonsense requirement, or it means something other than what it seems to say.
Yes, SQL Challenge 46. Interesting guesses. I guess we'll have to wait until we get a reply to see what he really means. Thanks.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
December 29, 2010 at 11:53 am
GSquared (12/29/2010)
Peter Brinkhaus (12/29/2010)
GSquared (12/29/2010)
One guy suggested A = 41, B = 42 (not sure where he got those numbers from)Probably hex values. 0x41 = 65, the ascii-value of a capital 'A', 0x42 = 66, a 'B', etc.
I thought of that too. But even if that's true, does that mean "Order By cast(column as varbinary(max))", or something else? Assuming that's even semi-official.
Sorry, I just tried to explain the numbers. I've no idea what the challenge is about. But "Order by cast(column as varbinary(max))" looks just like a binary sort to me. Try this
declare @t table(c varchar(50))
insert into @t(c) values ('AB')
insert into @t(c) values ('B')
insert into @t(c) values ('AD')
insert into @t(c) values ('aC')
insert into @t(c) values ('A0')
insert into @t(c) values ('BB')
insert into @t(c) values ('B!')
select
c, cast(c as varbinary(max))
from
@t
order by
cast(c as varbinary(max))
select
c
from
@t
order by
c COLLATE LATIN1_GENERAL_BIN
December 30, 2010 at 7:15 am
LutzM (12/29/2010)
GSquared (12/29/2010)
Peter Brinkhaus (12/29/2010)
GSquared (12/29/2010)
One guy suggested A = 41, B = 42 (not sure where he got those numbers from)Probably hex values. 0x41 = 65, the ascii-value of a capital 'A', 0x42 = 66, a 'B', etc.
I thought of that too. But even if that's true, does that mean "Order By cast(column as varbinary(max))", or something else? Assuming that's even semi-official.
The question has been answered in the related forum over at beyondrelational. 😛
The guy who suggested A=41 most probably is the one responsible for the challenge (I'm not sure if dishdy will be the forum master for this one...)
But yes, 41 is the hex value for 'A'.
To sort simply by varbinary(max) won't help since it doesn't recognize the string length. As per the example provided over there: the string 'AB' would be after 'B' since the number 42 is less than the number 4142... (= strong hint 🙂 ). 'nuff said from my side...
Yes, but the moment the string being sorted is longer than 1/2 of the max size of varchar(max), even by one character, his solution won't work.
Also, the only efficient way to sort a string that way is to parse it into rows, join to a table (CTE) of values for characters, and then reconcatenate it to sort that. The way to do that without procedural code is using a Numbers/Tally table. To process a 10-million character string (fits easily inside varchar(max)), you need a 10-million number Numbers/Tally table. To parse a billion-character string (still fits in varchar(max)), you need a billion-row Numbers table. Their sample Tally table, which you're supposed to use for this, doesn't even begin to approach what's needed for this.
Hence, this requirement actually breaks the challenge, because it doesn't match the rest of the requirements.
Also, and this is my main disagreement with it, the sorting algorithm thus becomes the biggest part of the code, and overshadows the actual challenge completely, in terms of complexity, lines of code, and in terms of performance. That's just dumb, in my arrogant opinion. It's like having a footrace to see who's the fastest runner, but in order to enter the race, your family has to juggle chainsaws while you run the 100 meter dash.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2010 at 7:50 am
GSquared, I feel the exact same way about it. I'm contemplating not going through the trouble of getting it sorted properly since it will end up being the most lines of code and the biggest performance hit. Odd requirement.
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply