April 2, 2009 at 1:48 pm
Just for the record, if I was so condescending and thought I was so much smarter than everyone else, why would I be posting a question in the first place?
You really do not want an answer to that question do you :ermm:
Far away is close at hand in the images of elsewhere.
Anon.
April 2, 2009 at 1:53 pm
Oh, I agree. There will be another change in the rules. That's how this works. But it's keeping me mildly entertained at this point.
mmmm, reminds me of a long thread many moons ago (and the person who started it). Condescending tone, incomplete and inaccurate data, unknown requirements and change the rules to fit their argument (whether correct or not!)
Will await the outcome (but not with baited breath) :hehe:
Far away is close at hand in the images of elsewhere.
Anon.
April 2, 2009 at 1:57 pm
Mr. Sanborn... have you bothered to test my solution yet?
Does it work or doesn't it?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2009 at 1:58 pm
Oh! and btw, does Occam's razor spring to mind here :crazy:
Far away is close at hand in the images of elsewhere.
Anon.
April 2, 2009 at 2:07 pm
You people really seem to hate hard questions. There isn't a single requirement that has "changed".
- It's a table of numbers that increment from left to right in every row and top to bottom in every column with empty (null, zero,whatever) cells scattered throughout such that you can't simply order by any one column (or group of columns) and get a correct sort.
Now where has that changed from the very first post? Condescending would be insulting people for not having an answer for the problem. But the problem has been right there in the first post from the start. I can't help it if people just can't see it.
April 2, 2009 at 2:08 pm
(Working on it, Bob H.)
April 2, 2009 at 2:29 pm
Very familiar tone from the OP. Isn't this the same guy who insisted cursor based solutions were superior to set based and played the "riddler" for a week or so recently? I cannot remember that guy's name as I eventually unsubscribed to his rant...his wife was a "DBA'..Anyway, they are not one and the same, it seems we have another in our midst!
-- You can't be late until you show up.
April 2, 2009 at 2:51 pm
Christian Buettner (4/2/2009)
Not sure what this is all about, but is this something you would expect?
SELECT *
, (SELECT COUNT(*) FROM b
WHERE ISNULL(col1,0) <= ISNULL(al.col1,9)
AND ISNULL(col2,0) <= ISNULL(al.col2,9)
AND ISNULL(col3,0) <= ISNULL(al.col3,9)
AND ISNULL(col4,0) <= ISNULL(al.col4,9)
AND ISNULL(col5,0) <= ISNULL(al.col5,9)
AND ISNULL(col6,0) <= ISNULL(al.col6,9)
AND ISNULL(col7,0) <= ISNULL(al.col7,9)
AND ISNULL(col8,0) <= ISNULL(al.col8,9)) AS Ordr
FROM b al
ORDER BY Ordr
Nice job, Chris 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 2, 2009 at 3:00 pm
(Looking at watch.) I'm guessing this thread is over.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2009 at 3:03 pm
Bob Hovious (4/2/2009)
Mr. Sanborn... have you bothered to test my solution yet?Does it work or doesn't it?
Looks like it doesnt(unless I made a mistake):
declare @u2bobnoxious table (ID int, Col1 int, Col2 int, Col3 int, col4 int)
insert into @u2bobnoxious
select 1, 235, 301, 349 ,425 union all
select 2, 0, 359, 432 ,NULL union all
select 3, NULL, NULL, NULL ,523 union all
select 4, 412, 466, 531 ,592 union all
select * from @u2bobnoxious
select * from @u2bobnoxious
order by isnull(+case when col1 = 0 then '' else str(col1,10,2) end,'')
+isnull(case when col2 = 0 then '' else str(col2,10,2) end,'')
+isnull(case when col3 = 0 then '' else str(col3,10,2) end,'')
+isnull(case when col4 = 0 then '' else str(col4,10,2) end,'')
--- continue this for however many columns you have in the table
returns
1235301349425
20359432NULL
4412466531592
3NULLNULLNULL523
(look at the last column in the last two rows)
What we need to understand is how the row comparison works.
If I understand it correctly it works like this:
Row X > Row Y if for each and every column x.col(n) > y.col(n).
The important part now is that per definition, the NULLs should be ignored in the sorting.
Therefore
x > NULL evaluates to true for each and every x
and
NULL > y evaluates to true for each and every y.
This is achieved via
ISNULL(col1,0) <= ISNULL(al.col1,9)
As a note, I am constraining my data here to be >= 0 and <= 9 for simplicity.
And as a final note: It is already late so please excuse any mistakes or unclear things... 🙂
Best Regards,
Chris Büttner
April 2, 2009 at 3:07 pm
Chris Morris (4/2/2009)
Christian Buettner (4/2/2009)
Not sure what this is all about, but is this something you would expect?
SELECT *
, (SELECT COUNT(*) FROM b
WHERE ISNULL(col1,0) <= ISNULL(al.col1,9)
AND ISNULL(col2,0) <= ISNULL(al.col2,9)
AND ISNULL(col3,0) <= ISNULL(al.col3,9)
AND ISNULL(col4,0) <= ISNULL(al.col4,9)
AND ISNULL(col5,0) <= ISNULL(al.col5,9)
AND ISNULL(col6,0) <= ISNULL(al.col6,9)
AND ISNULL(col7,0) <= ISNULL(al.col7,9)
AND ISNULL(col8,0) <= ISNULL(al.col8,9)) AS Ordr
FROM b al
ORDER BY Ordr
Nice job, Chris 😎
Thanks, but I am not sure yet whether the question poster is also of that opinion:-)
Best Regards,
Chris Büttner
April 2, 2009 at 3:10 pm
No that's correct. You ignore the zero in column 1.
That's exactly what my case statement is intended to accomplish.
So the sort order is
235....
359....
412....
523
The STR is to adjust the strings with spaces so if 359 becomes 35 the sort order becomes
_35....
235....
412....
523
The underscore represents a blank space that I can't show in the post.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 2, 2009 at 3:25 pm
Right, Bob, this is a "Shift Left When Zero" sorting problem. I realize that that description is inferior to the one given by the OP, but we are inferior intellects after all.
(hmm, which smiley face is for "sarcasm"?)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 3:26 pm
Sorry for the delay. I'm between meetings. And no, I'm not the cursor evangelist.
(Yes! My intellect is vastly superior to all the rest of you on this forum. That's why I come here to ask you all how to solve difficult problems that I haven't yet been able to. Hmmm. I guess maybe somebody finds logic in that.)
April 2, 2009 at 3:30 pm
No, it is the same as the 3, null, 9, situation, mathematically. A later value in a later column is higher than an earlier value in an earlier column.
As far as your condescension goes, I can't tell you why you wrote something. If I could read minds, I wouldn't be a DBA, I'd be doing something far more lucrative.
I'll take another look at your sort tomorrow. You are right, though, that this isn't just a simple Order By issue. On the other hand, your original post was missing a huge amount of critical information. So you might want to provide that on future issues.
- 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
Viewing 15 posts - 31 through 45 (of 180 total)
You must be logged in to reply to this topic. Login to reply