TOP clause WITH TIES
-
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 -
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 T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
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.
-
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!
-
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!
-
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.
-
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.
-
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
-
This was removed by the editor as SPAM
-
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 -
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 hope not. Knowledge in itself does not a database professional make. You can know the entirety of BOL (plus the necessary corrections) but if you aren't able to reason out a situation to identify which knowledge to apply and how to apply it, you'll be a crappy DBA/developer.
-
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 second Tom. Qotd should have mix of knowledge + reasoning test!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Great question!
I did spot the difference in the two columns, but messed up the logic when answering. :crazy:
-
Great question. Definitely need some more logic questions on QOTD.
-
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).
A mix of both, dressed up with some gratuitous misdirection, is what makes it fun for me. It is also a better reflection of many real world situations that way.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply