April 8, 2009 at 9:32 am
RBarryYoung (4/8/2009)
One problem is that you guys are testing against Easy data. Now why don't you try some HARD data:
Barry,
I've tested both of the solutions and unfortunately:doze: both of them are not working as per the rules. I guess we both needed some coffee before we can come up with another solution.
--Ramesh
April 8, 2009 at 9:46 am
Yeah, my data follows the rules, but plays havoc with any simple distribution assumptions.
😀
[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 8, 2009 at 10:17 am
RBarryYoung (4/8/2009)
Yeah, my data follows the rules, but plays havoc with any simple distribution assumptions.😀
No fair :sick: it's only 25% populated with numbers! If that!
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 8, 2009 at 10:20 am
I believe it's 23.5%. 🙂
And definitely not fair, but within the rules.
[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 8, 2009 at 10:41 am
Chris, with the logic of the other Chris, how should this sample data be sorted?
1 7 9
NULL 6 8
4 NULL 7
With the logic I implemented, the result should be
1 7 9
4 NULL 7
NULL 6 8
N 56°04'39.16"
E 12°55'05.25"
April 8, 2009 at 11:37 am
Peso (4/8/2009)
Chris, with the logic of the other Chris, how should this sample data be sorted?
1 7 9
NULL 6 8
4 NULL 7
With the logic I implemented, the result should be
1 7 9
4 NULL 7
NULL 6 8
Peso:
It is an invalid data set. The data set MUST have the property that the values of every column (disregarding nulls) can be in sorted order at the same time.
[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 8, 2009 at 11:49 am
I don't get it?
Why is that invalid sample data? Columns are ascending from left to right.
What OP needs is an algorithm to sort the records.
All assumptions this far is based in the sample data OP provided
SELECT 1, 2.35, 3.01, 3.49, 4.25, 4.79, 5.36, 5.82, 6.31
UNION SELECT 2, null, 3.59, 4.32, null, 5.21, null, null, 7.12
UNION SELECT 3, NULL, null, null, 5.23, 5.41, 6.33, 6.89, 7.99
UNION SELECT 4, null, 5.46, 5.31, 5.92, null, 6.87, 7.34, null
UNION SELECT 5, 5.12, 5.66, 6.31, 6.92, null, 7.87, 8.34, null
But what if the data is like this?
SELECT 1, 2.35, 3.01, 3.49, 4.25, 4.79, 5.36, 5.82, 6.31
UNION SELECT 2, null, 3.59, 4.32, null, 5.21, null, null, 7.99
UNION SELECT 3, NULL, null, null, 5.23, 5.41, 6.33, 6.89, 7.12
UNION SELECT 4, null, 5.46, 5.31, 5.92, null, 6.87, 7.34, null
UNION SELECT 5, 5.12, 5.66, 6.31, 6.92, null, 7.87, 8.34, null
7.12 and 7.99 has shifted places.
N 56°04'39.16"
E 12°55'05.25"
April 8, 2009 at 11:59 am
All assumptions this far seem to be based on page 1 decimal sample data.
That sample data is already sorted! OP writes "The question is how to sort unordered rows into this order."
And according to individual records, they can be descending too; see first OP post.
Values for individual columns are still ascending (disregarding NULLs).
But row-wise, the values columns-wise can be descending according to OP first post.
So I'll stick with my algorithm. First sort by lowest value for each record, and the second lowest, third lowest and so on...
N 56°04'39.16"
E 12°55'05.25"
April 8, 2009 at 12:19 pm
Peso (4/8/2009)
I don't get it?
Don't worry Peso, we spent the first three pages trying to figure this out.
Why is that invalid sample data? Columns are ascending from left to right.
Which is only half of the criteria (the horizontal half). The other half is that the data, when correctly sorted, will have the values of every column (vertically!) in sorted order (disregarding the nulls).
And THAT is the real question here, the OP just expressed it poorly.
But what if the data is like this?
SELECT 1, 2.35, 3.01, 3.49, 4.25, 4.79, 5.36, 5.82, 6.31
UNION SELECT 2, null, 3.59, 4.32, null, 5.21, null, null, 7.99
UNION SELECT 3, NULL, null, null, 5.23, 5.41, 6.33, 6.89, 7.12
UNION SELECT 4, null, 5.46, 5.31, 5.92, null, 6.87, 7.34, null
UNION SELECT 5, 5.12, 5.66, 6.31, 6.92, null, 7.87, 8.34, null
7.12 and 7.99 has shifted places.
Similar questions were asked back on the early pages. The upshot is that the data set is now not valid. Alternatively, you could say that according to the rules, the "proper" sorting of that dataset is indeterminate.
So I guess you could sort invalid datasets anyway that you wanted as long as you still sort the valid datasets correctly.
[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 8, 2009 at 12:23 pm
Peso, et al:
You could also look at the data from the dataset generator that I supplied. Sort it on [CorrectSequence], if you can understand why that is the correct sequence, then I think that the whole question will make more sense.
[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 8, 2009 at 12:24 pm
But isn't the CorrectSequence based on your understanding of OP's intention?
N 56°04'39.16"
E 12°55'05.25"
April 8, 2009 at 12:26 pm
The thing is that the "Left To Right Ascending" property of the columns is just a distraction. It has nothing to do with the actual problem, plus you cannot reorder the columns anyway, plus there is virtually no way to use this property in the solution.
[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 8, 2009 at 12:27 pm
Peso (4/8/2009)
But isn't the CorrectSequence based on your understanding of OP's intention?
Yes. I am contending that I have a correct understanding of the OPs intentions.
[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 8, 2009 at 12:29 pm
RBarryYoung (4/8/2009)
Yes. I am contending that I have a correct understanding of the OPs intentions.
Touché !
🙂
N 56°04'39.16"
E 12°55'05.25"
April 8, 2009 at 12:29 pm
Peso, the key to my understanding of the problem is the OPs third post back on the first page. Take a look at that and see what you think.
[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]
Viewing 15 posts - 121 through 135 (of 180 total)
You must be logged in to reply to this topic. Login to reply