title: Find person with highest home runs per team (DERBY/SQL)
body: <p>First off, this is homework, but I am not looking for the answer by any means. I am in a database class and we were given a data base with stats on the American Baseball league (teams, stats on each person etc) the schema definition for each table is below the question. The query I need to write is this: </p>
  
  <p><em>What pair of batters on the same team had the most homers as a pair? Your query should provide the rst and last names of each batter in the pair along with the number of home runs that each hit.</em></p>
  
  <p>So basically, just take the two highest home runs per team and see which team has the highest amount, but i'm having trouble writing a query that will generate the highest scoring person ( in terms of homeruns) per team. What I have so far is something like this.</p>
  
  <pre><code>select 
      nameFirst, nameLast, name, HR
  from 
      Players, Teams, Batting
  where 
      HR &gt;= ALL(select HR from Batting) 
      and Players.playerID = Batting.playerID;
  </code></pre>
  
  <p>which displays the correct team name (and I know this because it displays 30 tuples, and that's the number of teams in the DB), but the amount of home runs and the player's first and last name is all the same. (In this database, it is Prince Fielders for the Brewers because he has the most home runs in the database.) A tip on how to achieve is so it displays the correct person's first and last name is greatly appreciated!</p>
  
  <pre><code>CREATE TABLE Players
  (
      playerID VARCHAR(10), --A unique code asssigned to each player. The playerID
      --links the data in this file with records in the other files.
      nameFirst VARCHAR(50), --First name
      nameLast VARCHAR(50), --Last name
      bats CHAR(1), --Player's batting hand (left, right, or both)
      throws CHAR(1), --Player's throwing hand (left or right)
      PRIMARY KEY (playerID)
  );
  
  CREATE TABLE Teams
  (
      teamID CHAR(3), --Three-letter team code
      lgID CHAR(2), --Two-letter league code
      divID CHAR(1), --One letter code for the division the team player in
      Rank SMALLINT, --Position in final standings in that division
      G SMALLINT, --Games played
      W SMALLINT, --Games won
      L SMALLINT, --Games lost
      DivWin CHAR(1), --Division winner (Y or N)
      WCWin CHAR(1), --Wild card winner (Y or N)
      LgWin CHAR(1), --League champion (Y or N)
      WSWin CHAR(1), --World series winner
      name VARCHAR(50), --Team's full name
      park VARCHAR(255), --Name of team's home ballpark
      PRIMARY KEY (teamID)
  );
  
  CREATE TABLE Batting
  (
      playerID VARCHAR(10), --Player ID code
      yearID SMALLINT, --Will always be 2011 in data for this assignment
      stint SMALLINT, --Used to identify a particular continuous period that
      --a player played for one team during the season. For example, a player
      --who played during May for the Brewers, was then sent down to the
      --minors and came back to play again for the Brewers in August would
      --have two stints -- numbered 1 and 2
      teamID CHAR(3), --Three-letter team ID
      lgID CHAR(2), --Two letter league ID -- NL or AL
      G SMALLINT, --Number of games appeared in during this stint
      G_batting SMALLINT, --Number of games appeared in as a batter during this stint
      AB SMALLINT, --Number of at bats
      R SMALLINT, --Number of runs
      H SMALLINT, --Number of hits
      doubles SMALLINT, --Number of doubles
      triples SMALLINT, --Number of triples
      HR SMALLINT, --Number of home runs
      RBI SMALLINT, --Number of runs batted in
      SB SMALLINT, --Number of stolen bases
      CS SMALLINT, --Number of times caught trying to steal a base
      BB SMALLINT, --Number of base on balls (walks)
      SO SMALLINT, --Number of time player struck out
      IBB SMALLINT, --Number of intentional walks received
      HBP SMALLINT, --Number of time hit by pitch
      SF SMALLINT, --Number of sacrifice flied
      GIDP SMALLINT, --Number of times grounded into double play
      PRIMARY KEY (playerID, stint)
  );
  </code></pre>