Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TOP clause WITH TIES Expand / Collapse
Author
Message
Posted Wednesday, May 29, 2013 11:30 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 6:41 AM
Points: 907, Visits: 892
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
Post #1458039
Posted Thursday, May 30, 2013 12:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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 T-SQL question
Guidelines for answers on Performance questions

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

Follow me @Twitter

Post #1458062
Posted Thursday, May 30, 2013 3:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:32 AM
Points: 1,639, Visits: 5,721
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:32 AM
Points: 1,639, Visits: 5,721
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, December 12, 2014 5:31 AM
Points: 3,352, Visits: 1,490
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.
Post #1458125
Posted Thursday, May 30, 2013 5:14 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 4,611, Visits: 4,067
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
Post #1458136
Posted Thursday, May 30, 2013 5:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:21 PM
Points: 7,928, Visits: 9,653
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
Post #1458165
Posted Thursday, May 30, 2013 5:57 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 4,158, Visits: 5,556
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


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 6:41 AM
Points: 907, Visits: 892
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
Post #1458202
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse