Loading ...
Sorry, an error occurred while loading the content.

3759Re: [baseball-databank] Re: HACKS - Positions

Expand Messages
  • Bryan Walko
    May 8, 2009
      The query that is described is a neat trick and a good concept to know, but it's not a great permanent solution.

      ACCESS:
      SELECT yearID, teamID, Pos, Mid(Max(Str(1000+[g])+"_"+[playerid]),7) AS player
      FROM Fielding
      GROUP BY yearID, teamID, Pos;

      MySQL:
      SELECT `yearID`, `teamID`, `Pos`, SUBSTRING(MAX(CONCAT((1000+`g`),'_',`playerid`)),2,99) AS player
      FROM Fielding
      GROUP BY `yearID`, `teamID`, `Pos`

      Better MySQL:
      SELECT *
      FROM Fielding
      JOIN
      (SELECT `yearID`, `teamID`, `Pos`, MAX(`g`) AS `g`
      FROM Fielding
      GROUP BY `yearID`, `teamID`, `Pos`) as `mG`

      ON mG.`yearID` = f.`yearID`
      AND mG.`teamID` = f.`teamID`
      AND mG.`Pos` = f.`Pos`
      AND mG.`g` = f.`g`

      ORDER BY f.`yearID`, f.`teamID`, f.`Pos`

      On 5/4/09, William <williamwalburg@...> wrote:


      Is there a MySQL equivalent to this query? I'm struggling with converting it from Access to Mysql.

      --- In baseball-databank@yahoogroups.com, Tangotiger <tangotiger@...> wrote:
      >
      > There's a few posts here that might be of interest to
      > those working with primary positions:
      >
      > http://www.insidethebook.com/ee/index.php/site/comments/database_hacks/
      >
      > Tom
      >
      >
      > ---------------------------------------------
      > http://www.InsideTheBook.com
      > The Book--Playing The Percentages In Baseball
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      >
      > -----------------------------------
      >
      >
      >
      > __________________________________________________________
      > Now that's room service! Choose from over 150,000 hotels
      > in 45,000 destinations on Yahoo! Travel to find your fit.
      > http://farechase.yahoo.com/promo-generic-14795097
      >


    • Show all 20 messages in this topic