April 13, 2012 at 2:20 am
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 🙂
April 13, 2012 at 2:34 am
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.
April 13, 2012 at 2:58 am
This was removed by the editor as SPAM
April 13, 2012 at 4:06 am
Good question and leraned something today. I have never used WITH TIES before. I got it wrong but that was because I was trying to work it out in my head without ordering the data. Roll on lunch time!!!
April 13, 2012 at 5:14 am
very good question!!
I've never used WITH TIES !!!!
I learned anything today!!
thanks!!!!
April 13, 2012 at 5:45 am
Fairly easy question, I think. Hardest part was to sort the data :p
April 13, 2012 at 6:20 am
Thanks for the question
Iulian
April 13, 2012 at 6:44 am
Nice Question
I never use the Ties Option
April 13, 2012 at 7:08 am
masteratul25 (4/12/2012)
I am getting the below. Please correct me.:cool:
I am also getting an error when opening your attachment. Can you please re-post and attach screenshot as JPG?
Thanks,
"El" Jerry.
April 13, 2012 at 7:19 am
Hugo Kornelis (4/13/2012)
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 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.
Nice question elsewise though, I use top occasionaly, but have never used WITH TIES.... at first I thought it was a 2012 feature and I planned to complain about not specifying 2012, but looked it up to be sure, and was shocked that I hadn't noticed it before. 🙂
good question.
April 13, 2012 at 7:23 am
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.
2. The syntax used in this question is deprecated; Microsoft recommends always using parentheses with the TOP clause: TOP (5), TOP (5) WITH TIES, TOP (80) PERCENT, and TOP (80) PERCENT WITH TIES.
I realized about that after submitting the question, but couldn't find a chance to correct it. Though not fully deprecated yet Microsoft recommends the parentheses are used in the TOP clause.
Thank you for your comments.
"El" Jerry.
April 13, 2012 at 7:28 am
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.
I too thought this was a bit strange but thought Hmm, I guess I will have to change the way I write TOP queries. Then I thought when right clicking on a table and choosing "Select Top 1000 Rows" there are no () surrounding the number. Thanks for following up and clarifying.
April 13, 2012 at 7:29 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.
Yeah, but that's exactly the part I have trouble with. Was the intention of the QotD to test my understanding of TOP, WITH TIES, and PERCENT, of was it to test my ability to sort numbers?
But please don't let this minor comment put you off from submitting more questions! As I already said in my first comment, I do like the question!
April 13, 2012 at 7:30 am
Daniel Bowlin (4/13/2012)Then I thought when right clicking on a table and choosing "Select Top 1000 Rows" there are no () surrounding the number.
So there aren't. How very Microsoft!
April 13, 2012 at 7:31 am
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.
Nice question elsewise though, I use top occasionaly, but have never used WITH TIES.... at first I thought it was a 2012 feature and I planned to complain about not specifying 2012, but looked it up to be sure, and was shocked that I hadn't noticed it before. 🙂
good question.
I had learned this since the SQL Server 2000 days, but I guess this is a rarely used feature.
"El" Jerry.
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply