Teleporter12 Teleporter12 - 7 months ago 11
SQL Question

How do I use MAX and Count with two table

I have a query that needs to count the field Points. Then returns the highest value. This query does that fine however I now want to link another table 'Team(PlayerID) with Player(PlayerID), So it shows the player team details etc. I attempted to do that normally on how you would join table but keep getting errors. I also do not want to use the order by desc - First row only. (Oracle)

Query:

SELECT PlayerID, COUNT(Points)

Answer
SELECT t.*, p.* FROM
team t INNER JOIN
(SELECT PlayerID, COUNT(Points)
 FROM Player
 WHERE Points = 1
 group by PlayerID
 HAVING COUNT(Points) = (SELECT MAX(count(Points))
                         from Player
                         WHERE Points = 1
                         group by PlayerID)
) p
ON t.PlayerID = p.PlayerID