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

3769Re: HACKS - Positions

Expand Messages
  • William
    May 14, 2009
    • 0 Attachment
      You are right, it looks like I don't need to convert it to string.

      --- In baseball-databank@yahoogroups.com, Bryan Walko <bryanwalko@...> wrote:
      >
      > 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@...<williamwalburg%40sbcglobal.net>>
      > > 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>
      > > <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