

SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 5:13 AM
Points: 987,
Visits: 939


Comments posted to this topic are about the item TOP clause WITH TIES
 a haiku...
NULL is not zero NULL is not an empty string NULL is the unknown




Ten Centuries
Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:20 AM
Points: 1,372,
Visits: 1,567


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 rows
Hence, Statement 1 only returns more rows than Statement 2 when @X is even.
Nice Qotd Ron
~ Lokesh Vij
Guidelines for quicker answers on TSQL question Guidelines for answers on Performance questions
Link to my Blog Post > www.SQLPathy.com
Follow me @Twitter




SSCommitted
Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 1,695,
Visits: 5,819


I actually got the right answer, but only because I misread the questionI 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.




Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 21, 2015 8:48 AM
Points: 3,352,
Visits: 1,502


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!




SSCommitted
Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 1,695,
Visits: 5,819


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!




Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 21, 2015 8:48 AM
Points: 3,352,
Visits: 1,502


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 <= 19 BEGIN INSERT INTO @Table VALUES (@i, CAST(@i AS varchar(2))), (@i + 1, CAST(@i + 1 AS varchar(2))); SET @i = @i + 1; END
SET @x = ??? set an integer value between 1 and 20
statement 1 SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsInt; statement 2 SELECT 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.




SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:40 PM
Points: 5,637,
Visits: 5,100





SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 8,430,
Visits: 10,144


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




SSCarpal Tunnel
Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 4,438,
Visits: 5,852


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 selfimposed. “libera tute vulgaris ex”




SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 5:13 AM
Points: 987,
Visits: 939


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 zero NULL is not an empty string NULL is the unknown



