October 1, 2014 at 8:58 am
RLilj33 (10/1/2014)
I was thrown off by the values in the question. It states:If I have this data?
CustomerID Sales
1 100
2 50
3 50
4 120
5 110
Looks like Steve's already made an attempt at correcting the question! When I answered it, it said
If I have this data?
CustomerID Sales
1 100
2 50
3 50
4 120
Which may explain some of the inconsistent reactions...
October 1, 2014 at 9:06 am
I am sorry if I hurted someone. It was intended to be a joke !
October 1, 2014 at 10:20 am
Thanks Steve, great question and an opportunity to learn. And I guess for some an opportunity to grump as well.
The answer was clear and the numbers used were fine. I thought the 1,2,3,3,4 was just an example of the list with no spacing so it was between rank and rank dense. In looking at the difference between the two rank dense was the right choice.
I guess I have gotten to the point where I ask what is most right, not just sorta right according to the sample given.
Again thanks, I had to think but got it.
M.
Not all gray hairs are Dinosaurs!
October 1, 2014 at 10:23 am
Miles Neale (10/1/2014)
The answer was clear and the numbers used were fine.
The question was changed after some of the grumpy replies were posted. Originally, the answer was far from clear, with 5 values somehow coming out of 4 rows.
October 1, 2014 at 10:25 am
Toreador (10/1/2014)
Miles Neale (10/1/2014)
The answer was clear and the numbers used were fine.The question was changed after some of the grumpy replies were posted. Originally, the answer was far from clear, with 5 values somehow coming out of 4 rows.
Advantage to living on the west coast of the US, I guess.
Not all gray hairs are Dinosaurs!
October 1, 2014 at 12:39 pm
Miles Neale (10/1/2014)
Thanks Steve, great question and an opportunity to learn. And I guess for some an opportunity to grump as well.The answer was clear and the numbers used were fine. I thought the 1,2,3,3,4 was just an example of the list with no spacing so it was between rank and rank dense. In looking at the difference between the two rank dense was the right choice.
I guess I have gotten to the point where I ask what is most right, not just sorta right according to the sample given.
Again thanks, I had to think but got it.
M.
Agreed +1
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
October 1, 2014 at 3:47 pm
In hindsight, I perhaps should have included another row in the sample. I didn't intend for it to imply that 1,2,3,3,4 was the result, but rather that this was the type of thing to return. I believe that the original idea was 1,2,3,3. I thought people might confuse this and think it was a typo, so I added another number.
I'll award back points and add another row to the results
October 1, 2014 at 3:59 pm
I thought it was a excellent question. I also choose the wrong answer but I learned something new. Thank you Steve
October 1, 2014 at 4:55 pm
I should have gone with my gut on this one. When I got the same results using rank and dense_rank, I decided I must be misunderstanding the intent of the question and went with row_number. :unsure: I had very little confidence in getting this right because the sample output in the question confused me. That's what I get for focusing too much on the sample and not just following along with the overall intent.
October 2, 2014 at 1:19 am
Sean Pearce (10/1/2014)
Roland C (10/1/2014)
Another badly redacted question ! It gets a little boring :doze:You could always submit your own question.
I agree with you. I Think it's hard to create a question that have a clear answer, that is still enough challenging and can't be misinterpreted.
Stop complaining and give constructive feedback instead!
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
October 2, 2014 at 1:37 am
hakan.winther (10/2/2014)
Sean Pearce (10/1/2014)
Roland C (10/1/2014)
Another badly redacted question ! It gets a little boring :doze:You could always submit your own question.
I agree with you. I Think it's hard to create a question that have a clear answer, that is still enough challenging and can't be misinterpreted.
Stop complaining and give constructive feedback instead!
+1
😎
October 2, 2014 at 2:23 am
Great question, thanks Steve.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2014 at 4:24 pm
I've never used it before.
October 3, 2014 at 1:19 am
Looks like a typo in the question.
You have the values 100/50/50/120/110 in the posted question.
This would give the results 1, 2, 3, 4, 4 with both RANK() and DENSE_RANK(). You can't actually get a result of 1,2,3,3,4
But in the 'try it', the values are 100/50/50/120/25...
Bit of a discrepancy which invalidates the 'correct' answer.
October 3, 2014 at 2:59 am
nm.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply