Teleporter12 Teleporter12 - 2 years ago 84
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)


SELECT PlayerID, COUNT(Points)

Answer Source
SELECT t.*, p.* FROM
(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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download