Touchdowns & Missing Threads

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  August 6, 2013 at 3:44 pm

  #280886

  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/