Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

GWG maximum

GWG maximum

New Contributor

Can someone help me to write this query. I have written something like this but it is displaying the same results for several times

Question: Find out the player/s who had the highest GWG(you can repeat a player if the year is different). In the query results show the following details: year, player first and last names, date of birth (day, month and year), country of birth, and team name and league ID, their positions, and GWG.

SELECT a.year, e.FirstName, e.LastName, e.BirthDay, e.BirthMon, e.BirthYear, e.BirthCountry, c.name, c.lgID,e.pos, a.GWG FROM table_hockeyscoring a JOIN(SELECT year, max(GWG) GWG FROM table_hockeyscoring GROUP BY year)b ON (a.year = b.year AND a.GWG = b.GWG) JOIN table_hockeymaster e ON (e.playerID = a.playerID) JOIN table_hockeyteams c ON (c.lgID = a.lgID);

There are 3 different tables