Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TOP clause WITH TIES Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, May 29, 2013 11:30 PM
 Right there with Babe Group: General Forum Members Last Login: Thursday, December 12, 2013 9:48 AM Points: 748, Visits: 788
 Comments posted to this topic are about the item TOP clause WITH TIES -----a haiku...NULL is not zeroNULL is not an empty stringNULL is the unknown
Post #1458039
 Posted Thursday, May 30, 2013 12:55 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 5:15 AM Points: 1,324, Visits: 1,504
 Took some time to interpret the question, but eventually got that correct. The key to answer is to visualize how the data gets stored in table variable. Like for the first few iterations (3 iterations shown below), data would look like`----------------------| IASINT | IASSTRING |----------------------| 1 | 1 || 2 | 1 || 2 | 2 || 3 | 2 || 3 | 3 || 4 | 3 |----------------------`Now for statement "SET @x = ??? --set an integer value between 1 and 19", we try to imagine the output of the two statements (summary given below)When @X = 1; "statement 1" will return 1 row and "statement 2" will return 2 rows@X = 2; "statement 1" will return 3 rows and "statement 2" will return 2 rows@X = 3; "statement 1" will return 3 rows and "statement 2" will return 4 rows@X = 4; "statement 1" will return 5 rows and "statement 2" will return 4 rowsHence, Statement 1 only returns more rows than Statement 2 when @X is even.Nice Qotd Ron ~ Lokesh Vij Guidelines for quicker answers on T-SQL questionGuidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.comFollow me @Twitter
Post #1458062
 Posted Thursday, May 30, 2013 3:00 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 9:50 AM Points: 1,416, Visits: 4,747
 I actually got the right answer, but only because I misread the question--I assumed it was something to do with string sorting (where the sort order would become 1, 10, 11, 2 rather than 1, 2, 3, 4) and picked the nearest answer to what I thought would happen. Didn't even notice that the string value entered in the second value line was different from the integer for that line!That being the case, I wonder why the question setter didn't just use a pair of integers? Having the apparently completely irrelevant string conversion in there just confused the issue, IMHO.
Post #1458095
 Posted Thursday, May 30, 2013 4:05 AM
 Hall of Fame Group: General Forum Members Last Login: Thursday, December 05, 2013 6:32 AM Points: 3,352, Visits: 1,474
 Interesting question, and I didn't spot the different values in each column either.So there are two things going on here to confuse us. First is the fact that different values get put in each column because of the i vs i + 1 for the second insert in the loop. Second is the fact that the ordering is different in each case (which is what I thought the question was about). So for ordering by iAsInt we have 1,2,2,3,3,4,4,5,5... and ordering by iAsString we have 1,1,10,10,2,2,3,3,4...If the same values were put in both columns, we would still get different numbers of rows returned for all values of @x greater than 3 since ordering by iAsString would give 1,10,10,11,2,2,3,3...A good reminder (if we ever needed one) to put integers in integer columns!
Post #1458108
 Posted Thursday, May 30, 2013 4:32 AM
 Ten Centuries Group: General Forum Members Last Login: Yesterday @ 9:50 AM Points: 1,416, Visits: 4,747
 Duncan Pryde (5/30/2013)If the same values were put in both columns, we would still get different numbers of rows returned for all values of @x greater than 3 since ordering by iAsString would give 1,10,10,11,2,2,3,3...However, you'd only get more rows on the first statement returned for *even* values of X greater than 3. For example, if X=5, you get 1, 2, 2, 3, 3 from the integer version, and 1, 10, 10, 11, 2, 2 from the string version, whereas if X=6, you get 1, 2, 2, 3, 3, 4, 4 on the integer and 1, 10, 10, 11, 2, 2 on the string. That's if it worked as we both thought it did, which it clearly doesn't!
Post #1458114
 Posted Thursday, May 30, 2013 4:54 AM
 Hall of Fame Group: General Forum Members Last Login: Thursday, December 05, 2013 6:32 AM Points: 3,352, Visits: 1,474
 paul.knibbs (5/30/2013)Duncan Pryde (5/30/2013)If the same values were put in both columns, we would still get different numbers of rows returned for all values of @x greater than 3 since ordering by iAsString would give 1,10,10,11,2,2,3,3...However, you'd only get more rows on the first statement returned for *even* values of X greater than 3. For example, if X=5, you get 1, 2, 2, 3, 3 from the integer version, and 1, 10, 10, 11, 2, 2 from the string version, whereas if X=6, you get 1, 2, 2, 3, 3, 4, 4 on the integer and 1, 10, 10, 11, 2, 2 on the string. That's if it worked as we both thought it did, which it clearly doesn't!That's right - for odd values you'd get more rows returned by the second statement - which is the same behaviour as in the actual question. To separate out the two things completely to focus on varchar vs int ordering we could rewrite the statement as:`DECLARE @Table TABLE( iAsInt int, iAsString varchar(2)); DECLARE @i int, @x int;SET @i = 9;WHILE @i <= 19BEGIN INSERT INTO @Table VALUES (@i, CAST(@i AS varchar(2))), (@i + 1, CAST(@i + 1 AS varchar(2))); SET @i = @i + 1;ENDSET @x = ??? --set an integer value between 1 and 20--statement 1SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsInt;--statement 2SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsString;`This will give the same "values" in both columns, leaving the ordering as the only factor in determining the number of rows returned, which will be different for all values of @x - statement 1 having one more row for even values, statement 2 having one more for odd values.
Post #1458125
 Posted Thursday, May 30, 2013 5:14 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 12:54 PM Points: 2,787, Visits: 1,717
 That was a good one. The insert twisted my thinking a bit and I had to take the time to see how the data was going to be populated. I learned something today, so thank you.
Post #1458136
 Posted Thursday, May 30, 2013 5:46 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 2:14 PM Points: 7,954, Visits: 8,377
 That's a fun question. But maybe there will be complaints from some who think QotD should test only knowledge and not reasoning (except perhaps very trivial reasoning). Tom'S iomadh doigh a th’ air cu a mharbhadh gun a thachdadh le ìme
Post #1458165
 Posted Thursday, May 30, 2013 5:57 AM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 2:53 AM Points: 3,456, Visits: 4,591
 L' Eomot Inversé (5/30/2013)That's a fun question. But maybe there will be complaints from some who think QotD should test only knowledge and not reasoning (except perhaps very trivial reasoning).+1 ____________________________________________Space, the final frontier? not any more...All limits henceforth are self-imposed.“libera tute vulgaris ex”
Post #1458171
 Posted Thursday, May 30, 2013 6:58 AM
 Right there with Babe Group: General Forum Members Last Login: Thursday, December 12, 2013 9:48 AM Points: 748, Visits: 788
 L' Eomot Inversé (5/30/2013)That's a fun question. But maybe there will be complaints from some who think QotD should test only knowledge and not reasoning (except perhaps very trivial reasoning).I feared the same thing, but I figured one logic problem in a thousand QotDs isn't going to kill anyone. And like you said, it might be a fun diversion.As for the string conversion, I know I had a reason for doing it when I wrote the question but I'll be damned if I can remember what it was now. I think my original version of the question may have had to do with string sorting, but by the time I refined it down to its current form it no longer had anything to do with that. I guess I forgot to remove that part of the question. Sorry for the unnecessary confusion.ron -----a haiku...NULL is not zeroNULL is not an empty stringNULL is the unknown
Post #1458202

 Permissions