March 24, 2016 at 9:41 pm
Thanks for the question.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 25, 2016 at 5:04 am
Hi Steve, am I misreading this?
The correct answer is recognised as:-
GROUP BY Person HAVING COUNT(DISTINCT Played) = 3
I agree with that.
But then in the subsequent explanation, one of the other (wrong answers) is quoted:-
Thus, we use this query:
SELECT
rg.Person
FROM
dbo.RPSGame AS rg
GROUP BY rg.Person
HAVING COUNT(rg.Played) = 3
This will return a user that has at least 3 rows with different values for the "played" category.
That won't return a user that has at least three rows with different values for "played".
It will return a user that has played at least three games regardless of what they play.
March 25, 2016 at 12:35 pm
Andy Hogg (3/25/2016)
Hi Steve, am I misreading this?The correct answer is recognised as:-
GROUP BY Person HAVING COUNT(DISTINCT Played) = 3
I agree with that.
But then in the subsequent explanation, one of the other (wrong answers) is quoted:-
Thus, we use this query:
SELECT
rg.Person
FROM
dbo.RPSGame AS rg
GROUP BY rg.Person
HAVING COUNT(rg.Played) = 3
This will return a user that has at least 3 rows with different values for the "played" category.
That won't return a user that has at least three rows with different values for "played".
It will return a user that has played at least three games regardless of what they play.
Correct. Explanation was wrong. It's corrected.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 4, 2016 at 6:41 am
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2016 at 4:48 pm
ZZartin (3/24/2016)
Shouldn't it be COUNT(DISTINCT Played) >= 3, in case some decides to play 'switch kick to the nuts'? 😛
Good point.
April 20, 2016 at 6:52 am
l.vignando (3/24/2016)
Nice question, but awarding points for and titling "Group by" is a big hint, so alternatives without group should be automatically excluded.
By the same token, a whole lot of people have such a misunderstanding of "Group By" and resort to other methods to try to accomplish the task. It's a good question that covers several real life attempts by those that don't know.
--Jeff Moden
April 20, 2016 at 6:57 am
Joel Ewald (3/24/2016)
I think with the current data model and lack of constraints you would actually want both.WHERE Played = 'Rock' OR Played = 'Paper' OR Played = 'Scissors'
GROUP BY Person HAVING COUNT(DISTINCT Played) = 3
but the group by is key to the solution.
Heh... my next point was going to be that if "Played" were not constrained in some fashion, there would be no guarantee that only Rock, Paper, or Scissors would be present and the GROUP BY could return an incorrect condition. I think that's even more important than how to possibly solve the stated problem and, in real life, would consider it to be a missing requirement.
--Jeff Moden
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply