August 6, 2013 at 3:44 pm
It seems that a thread that requested help in determining the name of a quarterback, the max number of touchdowns thrown and year it happened was inadvertently deleted during a purge of lots of spam. I have part of the OP, DDL and data, and my reply. Steve Jones, SSC Editor, asked if I would repost so that the OP, and others, could see it, contribute, etc...
CREATE TABLE player_goals (Name varchar(50), Year int, Touchdowns int);
INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2007, 29);
INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2008, 25);
INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Drew Brees', 2009, 20);
INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2007, 19);
INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2007, 38);
INSERT INTO player_goals (Name, Year, Touchdowns) VALUES ('Steve Young', 2009, 44);
My reply
Try this and see if it works:
;with cte as
(
select Year, Name, touchdowns,
ROW_NUMBER() over(PARTITION by name order by touchdowns desc) RowNum
from player_goals
)
select Year, Name, touchdowns
from cte
where RowNum = 1
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply