CODEWITHSUNDEEP
sqlsql-servert-sql
Learn AspNet
Learn AspNet

Reputation: 1612

Find maximum catches in a year

Player Table :

Name   Catches  Year
Brady   500      2017
Tom     400      2017
Gyt     350      2017
Brady   600      2018
Tom     660      2018
Gyt     750      2018

I want it to return :

2017  Brady 
2018  Gyt

Explanation: Brady has more catches than anyone in 2017 and Gyt has 750 catches more than anyone in 2018

What I have tried to far :

SELECT NAME, SUM(Catches) as TotalCatches, Year 
from Player
Group Name, Year

Upvotes: 1

Views: 45

Answers (3)

LearnItDom
LearnItDom

Reputation: 140

Consider using RANK() or RowNumber() like below

SELECT p.*
FROM 
(
  SELECT [Year], [Name], Catches, RANK() OVER(PARTITION BY[YEAR] ORDER BY [Catches] DESC) RankOf
  FROM Player
) P
WHERE RankOf=1;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try using correlated subquery

SELECT a.* 
FROM Player a
WHERE Catches in (
    SELECT max(Catches) FROM Player b WHERE a.year = b.year 
)

Upvotes: 0

GMB
GMB

Reputation: 222492

With only one record per player and per year, aggregation is not needed.

You can use a correlated subquery to isolate the top record for each year :

SELECT p.* 
FROM Player p
WHERE NOT EXISTS (
    SELECT 1 FROM Player p1 WHERE p1.year = p.year AND p1.catches > p.catches
)

Starting with SQLServer 2017, window functions are the way to go :

SELECT year, name, catches 
FROM (
    SELECT p.*, ROW_NUMBER() OVER(PARTITION BY p.year ORDER BY p.catches DESC) rn
    FROM Player p
) x WHERE rn = 1

Upvotes: 0

Related Questions