Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TOP clause WITH TIES


TOP clause WITH TIES

Author
Message
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
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

Lokesh Vij
Lokesh Vij
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6213
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.
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
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!
paul.knibbs
paul.knibbs
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1972 Visits: 6213
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!
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
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.
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10238 Visits: 9560
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12002
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

Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5778 Visits: 7133
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”
ronmoses
ronmoses
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 Visits: 996
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search