April 2, 2009 at 3:34 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
I agree that this query does the job. I had been working on a query along the same lines, but you got there first.
April 2, 2009 at 3:34 pm
Chris Morris (4/2/2009)
jsanborn (4/2/2009)
Mr. MorrisNo such person has responded to this thread.
Heh. Wow, am I embarrassed. :blush:
[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:37 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.<<
It's not the same. The columns are also incremental top to bottom. If the 3 were in order, the 9 would be larger than the value in the same column in the next row. All the values in the table are incremental left to right in each row and top to bottom in each column with random empty cells.
April 2, 2009 at 3:45 pm
My apologies too, Chris. Although I thought you were a really cool guy.... :blush:
__________________________________________________
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:50 pm
Jsanborn, why don't you go back to the basics and try to come up with a MANUAL algorithm that comes up with the desired result in ALL cases? And then go and translate this into T-SQL if you can. In fact this may not even be possible once you consider all the permutations you may come across.
April 2, 2009 at 3:53 pm
jsanborn (4/2/2009)
>>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.<<It's not the same. The columns are also incremental top to bottom. If the 3 were in order, the 9 would be larger than the value in the same column in the next row. All the values in the table are incremental left to right in each row and top to bottom in each column with random empty cells.
Maybe I missed it in all the fracas, but what exactly is the datatype of these columns and just how many are there?
And yes, it does matter.
[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 4:02 pm
And by the way, since this seems to be just a sample of data to be sorted with 3 columns and five rows, have you considered what would happen to the poor SQL box if there were 20 columns and a million rows? Apart from the fact that the SQL would become totally unreadable of course, your DBA would probably want to throw you out of the 10th floor window, and your users might contemplate even worse ways of ending your stay on Earth.
To me this looks far more like the sorting should be done on the client side, that is assuming that you can tie down the rules in the first place.
And, please don't send cocky responses to people who are trying to help you out.
April 2, 2009 at 4:04 pm
Bob, I don't get your sorting.
You are comparing row1,col1 against row2,col2
But I really think that what you should do instead is
(rule 1) compare rowx,coly against rowx,colz (each column against another column in the same row - horizontal comparison)
(rule 2) compare rowx,coly against rowz,coly (each column against the same column in another row - vertical comparison)
Horizontally you do not need to do anything anymore since the tuples are already sorted.
But vertically you need to make sure each columm is sorted individually. If there is a null, you ignore it. You do NOT replace it with a value from a different column.
Still waiting for the solution from the OP though...
Best Regards,
Chris Büttner
April 2, 2009 at 4:23 pm
I agree with Chris. But now, in the presence of NULLs in any of the columns, how can simultaneous horizontal and vertical comparisons work without introducing some artificial replacement values for the NULLs that may well clash with valid data?
April 2, 2009 at 4:34 pm
Looking at the original sample the OP posted Chris may be right when he says
Horizontally you do not need to do anything anymore since the tuples are already sorted.
But then, the OP has been changing the rules along the way, so this may well not necessarily be the case. That's where it gets hairy. And again, how do the NULLs sort? By what the value in the previous row's previous column compared to the current row's previous column is? What if those are also NULLs? Recurse further left, then up? What if one ends up in column 1? And row 1?
What's the order of rows by the way? The identity column?
April 2, 2009 at 4:57 pm
Christian;
If I understand Mr. Sanborn correctly, and I still chastise him for lack of clarity, he wants to sort while ignoring zeros and nulls. I base this on the following quote:
The zeros are just nulls and not considered in the sorting.
Now, I'm going to replace the nulls/zeros in his original example with simple blanks. (Again, represented by underscores.)
Original:
ID Col1 Col2 Col3
1 3 4 5
2 1 2 _
3 _ _ 7
4 _ 3 4
5 4 _ _
Sorted:
ID Col1 Col2 Col3
2 1 2 _
4 _ 3 4
1 3 4 5
5 4 _ _
3 _ _ 7
Viewed this way (as a string pattern), the sort order becomes obvious.
1 2
3 4
3 4 5
4
7
However, the presence of decimals or numbers greater than a single digit complicates matters. The following order would clearly be incorrect.
1.2
12
2
300
40
So, we use the str function to space them appropriately as strings.
__1.2
__2
_12
_40
300
Try running the code below, and maybe my explanation will make more sense. This solution actually works without having to depend on subsequent rows getting continually higher values. To test that, try changing the 359 in the second row to 35.9.
declare @u2bobnoxious table (ID int, Col1 float, Col2 float, Col3 float, col4 float)
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 *
,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,'') as SortBy
from @u2bobnoxious
order by sortBy
I was waiting to hear if this was actually a correct solution to explain, but at least you should be able to see what I think is being asked for, and how this solution works.
Edited to improve formatting and correct typos.
__________________________________________________
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 6:56 pm
Bob,
Regardless of whether the data type is integer or decimal, I don't think your solution works. I think the only solution so far posted that works correctly is Christian's.
From what I understand of the OP's requirements, which weren't initially clear, the following rows are in the correct sorted order:
ID Col1 Col2 Col3
1 1 2 NULL
2 NULL 4 5
3 3 5 6
4 NULL NULL 7
Here's the SQL to insert the test data
DECLARE @u2bobnoxious TABLE (id int, Col1 float, Col2 float, Col3 float)
INSERT INTO @u2bobnoxious
SELECT 1, 1, 2, NULL UNION ALL
SELECT 2, NULL, 4, 5 UNION ALL
SELECT 3, 3, 5, 6 UNION ALL
SELECT 4, NULL, NULL, 7
Christian's method (following query) does return the rows in the expected order.
SELECT * FROM @u2bobnoxious A
ORDER BY (
SELECT COUNT(*) FROM @u2bobnoxious
WHERE (ISNULL(Col1, 0) <= ISNULL(A.Col1, 9))
AND (ISNULL(Col2, 0) <= ISNULL(A.Col2, 9))
AND (ISNULL(Col3, 0) <= ISNULL(A.Col3, 9))
)
However, your method incorrectly (I think) transposes rows 2 and 3
ID Col1 Col2 Col3
1 1 2 NULL
3 3 5 6
2 NULL 4 5
4 NULL NULL 7
SELECT * FROM @u2bobnoxious
ORDER BY (
SELECT 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, '')
)
April 2, 2009 at 7:13 pm
Hey Andrew 🙂
This is a case of differing interpretations about the true nature of the question.
My solution would in fact produce the last result you stated. It does so by design.
Only Mr. Sandborn can say which of us, if anyone, understood him correctly. If I misunderstood, I feel no shame about it. This isn't a hard problem to solve, but it is apparently a hard problem to define.
I believe the following quote is at the heart of the difference.
The values always increment across the row such that, when the rows are in order, the values in all columns also increment top to bottom.
I understood this to be a comment about the nature of the source data, that no rows would ever be inserted that would violate this pattern. I took this as a response to Chris Morris' hypothetical.
However, I can also see how you and Christian are reading it as a result requirement. At the moment, I am leaning towards joining in the group of supporters for Christian's solution, which is elegant. But again, only the OP knows for sure.
This is an excellent lesson in why it is much preferable to continually give visual examples of inputs and outputs rather than to rely on verbal descriptions. Simple patterns are capable of more than one interpretation. For example, which two of these three things go together?
Ping Pong Ball, Cricket, Elephant
Both the ping pong ball and the cricket are small.
Both the cricket and the elephant are animals.
So the answer is.... altogether now.... IT DEPENDS!
__________________________________________________
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 9:20 pm
Bob Hovious (4/2/2009)
...Ping Pong Ball, Cricket, Elephant
Both the ping pong ball and the cricket are small.
Both the cricket and the elephant are animals.
...
Both "Ping Pong Ball" and "Elephant" have three vowels.
[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 9:38 pm
I also think Christian's solution looks promising. It will be tomorrow afternoon before I can dig back into this.
Bob is also correct. The data in the table are numbers with these characteristics:
- The numbers in the rows increment left to right.
- The numbers in the columns increment top to bottom.
- Random cells are empty (null, zero) and aren't considered in the sort.
That's it. That's all it's been from the start. Since this was posted for a community of database professionals, I didn't think a great deal of detailed explanation was needed just like I didn't think it necessary to explain what a database or a table is. For those who claim that I keep changing the requirements, that is simply false. If you want to call me an arrogant jerk, you're certainly entitled to your opinion. But I think that throwing around baseless false insults makes you a bigger jerk than me.
Again, forgive me if I've seemed rude. But I was not thinking that I'm so smart and you're all so dumb. Given the first response that was offered,
I must be missing something. Why would a very simple Order By not do what you need?
my feeling was that maybe some people were just in a hurry to answer questions without bothering to take a minute to look at the examples and see what was there. So then Chris Morris says,
Where would you put this row?
ID Col1 Col2 Col36 3 0 9
and I replied
The values always increment across the row such that, when the rows are in order, the values in all columns also increment top to bottom.
to which Chris replied
jsanborn you still haven't answered my question from above.
and so on and so forth throughout much of this lengthy thread. It was just getting a little frustrating, and I think understandably so.
Viewing 15 posts - 46 through 60 (of 180 total)
You must be logged in to reply to this topic. Login to reply