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

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

Expand Messages
  • Bryan Walko
    May 14, 2009
      If you just want an arbitrary winner, it's very easy to do it in the fashion that the hack does (which is just choosing the player with the highest ID, alphabetically).

      It just becomes:

      MySQL:
      SELECT f.`yearID`, f.`teamID`, f.`Pos`, MAX(`playerID`) as `playerID`
      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`

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

      Using CHAR
      I believe that the way you are using CHAR is unneeded for the query that you are writing. When you concatenate a number with a string in MySQL, it automatically converts it to a string for you.  MS Access may need the conversion done manually by the user, but it's unnecessary in MySQL.


      On 5/11/09, Tangotiger <tom@...> wrote:


      Bryan,

      You don't have a tie-breaker in case someone plays the same number of
      games at two positions, do you?

      I would suggest that any alternative solutions be verified to existing
      solutions.

      Tom



      > 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<baseball-databank%40yahoogroups.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
      >> >
      >>
      >>
      >>
      >

      ---------------------------------------------
      The Book--Playing The Percentages In Baseball
      http://www.InsideTheBook.com


    • Show all 20 messages in this topic