October 1, 2014 at 5:07 am
Another question where the answer has contradicting data than the question itself! :w00t:
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
October 1, 2014 at 5:14 am
This was removed by the editor as SPAM
October 1, 2014 at 5:32 am
Are you guys serious? The example of what the results should be was just that - an example. If the example was 1, 2, 3, 3 then either RANK or DENSE_RANK would work. The example of 1, 2, 3, 3, 4 was to show that no skips were wanted in the result set, just like the requirements stated right before the example. The example was to give clarification to the stated question.
October 1, 2014 at 5:42 am
Thanks for the question, Steve.
Even though there is clearly an error in the listing of possible results, the requirements clearly point to the use of the Dense_Rank funciton, IMO.
... I get a rank of each number, with no skips in the results,...
---------------
Mel. 😎
October 1, 2014 at 5:42 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.
+1000
---------------
Mel. 😎
October 1, 2014 at 6:37 am
Toreador (10/1/2014)
Eirikur Eiriksson (10/1/2014)Wrong assumption, nothing wrong with the question.
If you think that then I suggest you re-read the question!
I'm intrigued by this comment, could you elaborate further?
😎
October 1, 2014 at 6:38 am
erwin.oosterhoorn (10/1/2014)
I agree that there should be at least one more record in the example to make sense of the suggested answer. But if you read this part:If I want to get the rankings of customers, and ensure that I get a rank of each number, with no skips in the results, which function do I use?
you can use the example data and determine that the answer must be DENSE_RANK(), as that will give you 1,2,3,3,4 if there are more records, and the Row_number() will give you 1,2,3,4,5.
Just my interpretation of the question
Except that the data and the example values do not match. Therefore, one must guess the intention. Are we to assume duplicate values are to be assigned the same ranks; or that the extra 3 was a typo. Because strictly speaking, row_number() will give you "a rank of each number, with no skips in the results". It does not give the same rank to repeated values, thus is an inferior choice for a true ranking, but it does do what the question asks.
And if you want to get deeply literal (and grammatical) the article 'a' is inherently singular, so "a rank of each number" disallows any repeated ranks. Maybe that is being overly literal, but data does not lie and accuracy is critical :hehe:.
October 1, 2014 at 6:41 am
Eirikur Eiriksson (10/1/2014)
Toreador (10/1/2014)
Eirikur Eiriksson (10/1/2014)Wrong assumption, nothing wrong with the question.
If you think that then I suggest you re-read the question!
I'm intrigued by this comment, could you elaborate further?
😎
Maybe the question should just include the answer so people don't have to think. Erikur, I agree that there was nothing wrong with the question. An example is...wait for it...an example.
October 1, 2014 at 6:47 am
The question includes example data. And the required result set. Unfortunately the result set cannot be obtained from the data.
It's clearly an error, and Steve will be along sooner or later to say as much.
October 1, 2014 at 6:52 am
GilaMonster (10/1/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.
This kind of reaction is why I don't submit questions any more.
That's a shame. I look at the QotD as a learning opportunity. I always answer the question off the top of my head, no research beforehand. If I'm wrong, I've learned something new. If I'm right, I still check the discussion about the QotD to see what other tidbits someone has dropped. Either way, I usually learn something.
If this type of reaction is stopping someone like Gail from posting a QotD, you know it's discouraging others from doing the same. I imagine for most of us, it's very difficult to put yourself out there (community intelligence is intimidating).
October 1, 2014 at 6:57 am
GilaMonster (10/1/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.
This kind of reaction is why I don't submit questions any more.
That's too bad, as I imagine your questions would be interesting.
I say ignore the peanut gallery and submit your questions anyway.
October 1, 2014 at 7:43 am
What I see as another trick question. In your example data you see before answering the question you have values that would give you the rankings of:
CustomerIDRank
4 1
5 2
1 3
2 4
3 4
For DENSE_Rank and the same results for RANK.
But in your sample data you show after answering the question you changed the value of sales for customerID of 5 to 25 instead of the 110 you showed before you answered.
Looks like someone needs to proof read these before they get posted.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
October 1, 2014 at 7:46 am
Thanks for the question Steve, very interesting one.
At first, I did not see the difference in RANK and the DENSE_RANK for the given data ...
1> CREATE TABLE custsale
2> ( Customer ID INT, Sales INT);
3> GO
Msg 102, Level 15, State 1, Server RAGHU-PC\SQLSVRDEVED12SP1, Line 2
Incorrect syntax near 'INT'.
1> CREATE TABLE custsale
2> ( CustomerID INT, Sales INT);
3> GO
1> INSERT custsale VALUES (1, 100), (2, 50), (3, 50), (4, 120)
2> GO
1> SELECT customerid, (RANK() over (order by sales DESC)) as Rank from custsale as cd
2> GO
customerid Rank
----------- --------------------
4 1
1 2
2 3
3 3
1> SELECT customerid, (dense_rank() over (order by sales DESC)) as Rank from custsale as cd
2> go
customerid Rank
----------- --------------------
4 1
1 2
2 3
3 3
and then I just added couple of more rows to see the difference.
1> INSERT custsale VALUES (5, 110), (6, 25), (7, 32)
2> GO
(3 rows affected)
1> SELECT customerid, (dense_rank() over (order by sales DESC)) as Rank from custsale as cd
2> GO
customerid Rank
----------- --------------------
4 1
5 2
1 3
2 4
3 4
7 5
6 6
(7 rows affected)
1> SELECT customerid, (rank() over (order by sales DESC)) as Rank from custsale as cd
2> GO
customerid Rank
----------- --------------------
4 1
5 2
1 3
2 4
3 4
7 6
6 7
(7 rows affected)
1>
Thank you for the post, very good experience for me in learning today. 🙂
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
October 1, 2014 at 8:16 am
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
In the explanation, Try It has this:
INSERT INTO #CustomerDemo
( CustomerID
, Sales
)
VALUES
( 1, 100), ( 2, 50), (3, 50), (4, 120), (5, 25)
For CustomerId 5, the value in the question was 110; in the explanation this was 25. Using Sales of 110 results in 1, 2, 3, 4, 4 for both RANK() and DENSE_RANK().
October 1, 2014 at 8:19 am
Nice question, Steve. I understood that you were looking for DENSE_RANK, but your data did not match up with your expected results (especially since the initial version in the e-mailed newsletter only had four data points), and the data in the answer was different from the data in the question. It's still good to help people understand the difference between ROW_NUMBER, RANK and DENSE_RANK, which was obviously your intent.
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply