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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy