April 2, 2009 at 11:15 am
jsanborn (4/2/2009)
Mr. Morris
No such person has responded to this thread.
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 11:51 am
Ms. Morris?
April 2, 2009 at 12:33 pm
jsanborn (4/2/2009)
I'm afraid the coalesce won't work either. It's essentially doing the same thing as the case statement. Comparing values from adjacent columns will not produce a correct result.
On every test case you've posted so far, the coalesce version that I posted has worked. Please post sample data that makes it not work.
- 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
April 2, 2009 at 12:46 pm
The coalesce function will use the next column value if the first one is null, thereby doing much the same thing accomplished with using case statements as suggested earlier. As I've tried to make clear, the occurrence of nulls is random. Therefore, if you are comparing the forth or fifth column value of one row with the first column value of the next row, you won't get a correct sort.
April 2, 2009 at 12:59 pm
This is a hard problem to solve with SQL, and there is also no guarantee of an unambiguous sort order.
e.g.
row col1 col2
---- ---- ----
R1 1.0 2.0
R2 null 2.5
R3 2.4 null
R4 4.0 4.5
In this case, there's no well-defined order for rows R2 and R3.
Adding a 3rd column with non-null values in both rows R2 and R3 disambiguates the sort order, but the expression COALESCE(col1, col2, col3) would give the wrong sort order in this case.
row col1 col2 col3
---- ---- ---- ----
R1 1.0 2.0 2.7
R2 null 2.5 2.8
R3 2.4 null 3.2
R4 4.0 4.5 null
Sorry - no solution yet, but it is an interesting problem.
Is it a real-world problem for you?
April 2, 2009 at 12:59 pm
Sir, I will bow to your obviously superior knowledge of all things in all universes. But, the results from the first sample were correct. Here are the results from your second sample:
create table #T (
ID int identity primary key,
Col1 float,
Col2 float,
Col3 float,
Col4 float,
Col5 float,
Col6 float,
Col7 float,
Col8 float);
insert into #T (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8)
select 2.35, 3.01, 3.49, 4.25, 4.79, 5.36, 5.82, 6.31 union all
select null, 3.59, 4.32, null, 5.21, null, null, 7.12 union all
select 3.88, null, null, 5.23, 5.41, 6.33, 6.89, 7.99 union all
select 4.12, 4.66, 5.31, 5.92, null, 6.87, 7.34, null;
select *
from #T
order by
coalesce(Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8),
coalesce(Col2, Col3, Col4, Col5, Col6, Col7, Col8),
coalesce(Col3, Col4, Col5, Col6, Col7, Col8),
coalesce(Col4, Col5, Col6, Col7, Col8),
coalesce(Col5, Col6, Col7, Col8),
coalesce(Col6, Col7, Col8),
coalesce(Col7, Col8),
col8;
Results in:
IDCol1Col2Col3Col4Col5Col6Col7Col8
12.353.013.494.254.795.365.826.31
2NULL3.594.32NULL5.21NULLNULL7.12
33.88NULLNULL5.235.416.336.897.99
44.124.665.315.92NULL6.877.34NULL
These columns and rows are in the order and pattern you have specified. There is no mathematically possible way for them to NOT be in it.
So, provide the sample data, the test case, where that solution DOES NOT work. You have not done so thus far. You've been insulting. You've failed to provide necessary data to the solution of the problem. You've done all that, but you haven't disproved my solution.
So, disprove the solution. Simple as that. Once you have done so, I will refine the solution and provide one that does work. Simple as that.
Based on the data you have provided, this solution works in all cases. Thus, since you claim it does not work, there is obviously data that does not fit the patterns and rules you have provided thus far. (Well, either that or you're wrong about it not working. But since you being wrong is impossible, I'm discounting that.)
- 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
April 2, 2009 at 1:05 pm
As a side note, my solution also solves andrewd.smith's samples correctly.
The only ones it failes on are ones that we've been told are impossible, as per my earlier posts. (3,0,9 and 5,4,3 are the combinations it fails on. We're told those kind of patterns can't happen in this data set.)
- 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
April 2, 2009 at 1:16 pm
ID Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
1 2.35 3.01 3.49 4.25 4.79 5.36 5.82 6.31
2 NULL 3.59 4.32 NULL 5.21 NULL NULL 7.12
3 NULL NULL NULL 5.23 5.41 6.33 6.89 7.99
4 4.12 4.66 5.31 5.92 NULL 6.87 7.34 NULL
how about here.
April 2, 2009 at 1:27 pm
declare @u2bobnoxious table (ID int, Col1 int, Col2 int, Col3 int)
insert into @u2bobnoxious
select 1, 3, 4, 5 union all
select 2, 1, 2, 0 union all
select 3, 0, 0, 7 union all
select 4, 0, 3, 4 union all
select 5, 4, 0, 0
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,'')
-- continue this for however many columns you have in the table
__________________________________________________
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:28 pm
Nope. Won't work on that one. That's the equivalent of the 3,null,9 case. Col4 of row 4 is lower than Col3 of row 3. (Col5 and Col4 of the same rows also violate that rule.) You had already unequivocally stated that such could not and would not happen.
So, what's the actual rule on the data?
- 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
April 2, 2009 at 1:29 pm
G, he just wants a string. Zeroes = nulls = ''s. Make sure that leading zeroes are accounted for and decimals and numbers larger than 1 char. Of course, he can always say FLOAT now and break my solution.
I now predict Mr. Sanborn will dramatically reveal some other previously undisclosed requirement that everyone should have just OBVIOUSLY known about. That's a childish game.
[font="Arial"]Edited to add, then delete, my personal opinions about the OPs manners and upbringing.[/font]
__________________________________________________
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:37 pm
Bob Hovious (4/2/2009)
G, he just wants a string. Zeroes = nulls = ''s. Make sure that leading zeroes are accounted for and decimals and numbers larger than 1 char. Of course, he can always say FLOAT now and break my solution.I now predict Mr. Sanborn will dramatically reveal some other previously undisclosed requirement that everyone should have just OBVIOUSLY known about. That's a childish game.
[font="Arial"]Edited to add, then delete, my personal opinions about the OPs manners and upbringing.[/font]
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.
- 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
April 2, 2009 at 1:43 pm
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
Best Regards,
Chris Büttner
April 2, 2009 at 1:43 pm
The last sample I posted was not at all equivalent to that other "3, null, 9" case. (If that is what was being referred to.) In that case, sorting the 3 in order caused the 9 in the same row to be out of order in its column. All I did in my previous post was null one more value in the same table. Every value is still incremental top to bottom.
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?
April 2, 2009 at 1:45 pm
... and I don't know who's asking for strings. I never said anything about strings.
Viewing 15 posts - 16 through 30 (of 180 total)
You must be logged in to reply to this topic. Login to reply