April 12, 2012 at 10:47 pm
Comments posted to this topic are about the item SELECT options
April 12, 2012 at 11:26 pm
Very very nice question (not only because my name is in it :-D)
Never used WITH TIES before, so I definately learned something.
Good question to close the week.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 12, 2012 at 11:49 pm
I am getting the below. Please correct me.:cool:
April 12, 2012 at 11:49 pm
I am getting the below. Please correct me.:cool:
April 12, 2012 at 11:57 pm
Nice question....
-----------------
Gobikannan
April 13, 2012 at 12:08 am
masteratul25 (4/12/2012)
I am getting the below. Please correct me.:cool:
I get an error when I try to open your attachment.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 12:11 am
Good Question. Made me think a lot.
Though I knew the existence of WITH TIES option, I never really used it and never bothered to get the exact details of how it works.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 13, 2012 at 12:47 am
Definitely had to earn my point today - took a fair bit of thought.
Good question, thank you
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
April 13, 2012 at 1:40 am
Interesting question, and I learned something new from it.
Cheers!
April 13, 2012 at 1:51 am
Nice week closing question.
I've never used WITH TIES so learned something new yet again.
Have a great weekend folks.
😀
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
April 13, 2012 at 2:05 am
Good question about some little known features (PERCENT and WITH TIES) of the TOP clause.
Two minor quibbles:
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.
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.
April 13, 2012 at 2:06 am
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
April 13, 2012 at 2:09 am
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
The sort is descending, so you need to start with the highest numbers.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 13, 2012 at 2:10 am
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.
April 13, 2012 at 2:13 am
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!
Viewing 15 posts - 1 through 15 (of 60 total)
You must be logged in to reply to this topic. Login to reply