April 13, 2012 at 7:33 am
EL Jerry (4/13/2012)
I entered the values in random order so it would not be that easy or obvious when you look at it on the screen 🙂 . That way you would have to at least manually sort them using paper and pencil.
I liked the question but the ordering made it more painful than it needed to be. I suddenly found myself acting like the sql engine and that was not needed for this. I agree with Hugo that if these had been in order it would have made the question a lot better. The point was to test knowledge of with ties not to test if somebody wants to spend extra time to sort the unsorted list.
Looking forward to your next QOTD!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 7:36 am
EL Jerry (4/13/2012)
mtassin (4/13/2012)
I just pasted it into Excel and used text to columns, but the same principal applies. Looking at 10 rows not ordered by the amount that the top N clause followed, I realized that first I had to order them so I could figure out where top 80% or top 5 went.
Actually, TOP... WITH TIES requires that ORDER BY is specified.
Sure, but the original data was unsorted... which meant I had to start fiddling with it to put it in the order specified by the queries in the question.
My complaint was that I had to do that. The author could have sorted the data by salary descending so that we could concentrate on the actual question, and not sorting the data 🙂
April 13, 2012 at 7:56 am
Nice question. As others have said, I don't use the with ties much, but it was good to have to study it a bit. Thanks for the effort! At least this feature works like you'd expect.
However, how does it handle ties with NULLs?
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
April 13, 2012 at 8:01 am
Thomas Abraham (4/13/2012)
Nice question. As others have said, I don't use the with ties much, but it was good to have to study it a bit. Thanks for the effort! At least this feature works like you'd expect.However, how does it handle ties with NULLs?
Of course.
;with TopTest (SomeValue)
as (
select null union all
select null union all
select null union all
select 4 union all
select 4 union all
select 6 union all
select 7
)
select Top 2 with ties *
from TopTest
order by SomeValue
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 13, 2012 at 8:04 am
Interesting! Also
select Top 5 with ties *
from TopTest
order by SomeValue desc
Seems like in this case, NULL = NULL
April 13, 2012 at 8:16 am
I did not find it burdensome to manually sort the 10 values with pencil and paper.
I loved this question. I have never heard of WITH TIES and I was very surprised to learn it was not a feature introduced in one of the newer versions of SQL Server.
April 13, 2012 at 8:29 am
danielfountain (4/13/2012)
Hugo Kornelis (4/13/2012)
danielfountain (4/13/2012)
I am confused.How come this is supposed to bring back 6 rows?
select top 5 with ties * from SSCSales order by cMonthSales desc
There are 4 records with 3500. So those will return. The fifth element would be 4200 for which there are 3.
As with ties will bring through all of the 4200`s i make that 7?
What am i doing wrong?
Dan
Are you overlooking the "desc" option in the order by clause?
Since sales are sorted in descending order, the top 5 are 6100, 5700, 5400, and two of the three 4200's. The WITH TIES adds the third 4200.
Doh!!! I thought i was missing something obvious as no one else had questioned it. Long week!
Me too! After researching the with ties option, I fell apart by missing the desc option...
April 13, 2012 at 8:35 am
Thanks for the question:cool:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 13, 2012 at 8:40 am
great question to round out the week. cheers!
April 13, 2012 at 8:45 am
Sean Lange (4/13/2012)
Of course.
;with TopTest (SomeValue)
as (
select null union all
select null union all
select null union all
select 4 union all
select 4 union all
select 6 union all
select 7
)
select Top 2 with ties *
from TopTest
order by SomeValue
Thanks to you and Toreador for the code posting. The NULL = NULL result was what I expected.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
April 13, 2012 at 8:53 am
Good one.
M&M
April 13, 2012 at 9:41 am
It took a few minutes to figure this one out... Thanks, Gerardo!
April 13, 2012 at 10:59 am
Good question. Didn't know about the WITH TIES clause.
April 13, 2012 at 11:37 am
EL Jerry (4/13/2012)
Hugo Kornelis (4/13/2012)
Good question about some little known features (PERCENT and WITH TIES) of the TOP clause.1. It would have been better to present the data ordered by salary. I first tried to order them by head, but I was constantly afraid that I'd overlook something. I ended up creating a table, putting in the salary values from the question, and running a SELECT * FROM ThatTable ORDER BY value DESC, just so that I could see the values in order to work out the correct example.
I entered the values in random order so it would not be that easy or obvious when you look at it on the screen 🙂 . That way you would have to at least manually sort them using paper and pencil.
I tend to agree with Hugo. I didn't bother to sort, just typed the following into a notepad:
423
354
57 1
61 1
54 1
as counting is reasonably easy and there are few enough different values to sort in my head. But it would have been nice if rows with the same cMonthSales value had been adjacent, so that the counting was easier still. 😉
Regardless of that, it is a good question.
Tom
April 13, 2012 at 2:06 pm
Hugo Kornelis (4/13/2012)
Toreador (4/13/2012)
Hugo Kornelis (4/13/2012)The syntax used in this question is deprecated
Is it definitely deprecated? The help page doesn't say so, just that the parentheses are optional for backward compatibility and recommends their use. But this wouldn't be the first time that the online help was missing something 🙂
I think you may be right. I guess I read too much into that BOL quote when I first saw it, and then remembered my cocnclusion instead of the actual text. I can't find any deprecation note anywhere.
This is slightly off-topic, but a separate reason (aside from "possible future deprecation") to use parentheses is so that you can use an expression in the TOP() clause, like this:
SELECT TOP(@n) col FROM Foo;
I'm trying to always use parentheses now, so I don't have to remember which syntax will work without parentheses and which will not.
Rich
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply