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

2006 minor league statistics in CSV format

Expand Messages
  • Theodore Turocy
    Hi all (and apologies for crossposting): I figured to do some getting while the getting was good, and compile a set of minor league statistics for the 2006
    Message 1 of 20 , Jan 17, 2007
    • 0 Attachment
      Hi all (and apologies for crossposting):

      I figured to do some getting while the getting was good, and compile a
      set of minor league statistics for the 2006 season while much of the
      information is still available on various websites.

      The results can be gotten at

      http://econweb.tamu.edu/turocy/minors/milb2006-2007.01.17.zip

      This has (almost) complete batting and pitching for all players and
      teams. Please see the README file for details on what's missing.

      Everyone's welcome to use this information as they see fit. While I
      have tried to be careful (and while the process of building this was
      largely automated), these have not yet been checked against external
      sources. Please report any errors or additions to me off-list.

      Ted
      --
      drarbiter@... - AMDG
    • Tangotiger
      There s a few posts here that might be of interest to those working with primary positions:
      Message 2 of 20 , Jan 17, 2007
      • 0 Attachment
        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
      • mkmv67
        Cool, thanks, if I had to recommend adding one major thing to it, would be the team affiliations to the minor league teams (when applicable)....although it
        Message 3 of 20 , Jan 22, 2007
        • 0 Attachment
          Cool, thanks, if I had to recommend adding one major thing to it,
          would be the team affiliations to the minor league teams (when
          applicable)....although it wouldnt be hard for someone that downloads
          it to manually add them, just sayin.....

          --- In baseball-databank@yahoogroups.com, "Theodore Turocy"
          <drarbiter@...> wrote:
          >
          > Hi all (and apologies for crossposting):
          >
          > I figured to do some getting while the getting was good, and
          compile a
          > set of minor league statistics for the 2006 season while much of the
          > information is still available on various websites.
          >
          > The results can be gotten at
          >
          > http://econweb.tamu.edu/turocy/minors/milb2006-2007.01.17.zip
          >
          > This has (almost) complete batting and pitching for all players and
          > teams. Please see the README file for details on what's missing.
          >
          > Everyone's welcome to use this information as they see fit. While I
          > have tried to be careful (and while the process of building this was
          > largely automated), these have not yet been checked against external
          > sources. Please report any errors or additions to me off-list.
          >
          > Ted
          > --
          > drarbiter@... - AMDG
          >
        • Theodore Turocy
          ... But of course. The focus of this release was preserving the major tabular data that could be extracted in an essentially automated way. Of course, if
          Message 4 of 20 , Jan 22, 2007
          • 0 Attachment
            On 1/22/07, mkmv67 <afn777@...> wrote:

            Cool, thanks, if I had to recommend adding one major thing to it,
            would be the team affiliations to the minor league teams (when
            applicable)....although it wouldnt be hard for someone that downloads
            it to manually add them, just sayin.....





             


            But of course.  The focus of this release was preserving the major tabular data that could be extracted in an essentially automated way.

            Of course, if anyone were to fill in the affiliations in a new column and send them along, they'd get included even faster than if I have to do it myself!

            TT
            --
            drarbiter@... - AMDG
          • mkmv67
            Hey doc, I uploaded the teams file with the franchise for each. Anyone wanting to integrate this to the BDB would have to make a new column and add the
            Message 5 of 20 , Jan 26, 2007
            • 0 Attachment
              Hey doc,
              I uploaded the teams file with the franchise for each. Anyone wanting
              to integrate this to the BDB would have to make a new column and add
              the pertinent franchiseID for each. ALso, they would have to
              still "relate" the playerteam to this team on the full table. I don't
              have time to add it today, perhaps another time.

              --- In baseball-databank@yahoogroups.com, "Theodore Turocy"
              <drarbiter@...> wrote:
              >
              > On 1/22/07, mkmv67 <afn777@...> wrote:
              > >
              > > Cool, thanks, if I had to recommend adding one major thing to
              it,
              > > would be the team affiliations to the minor league teams (when
              > > applicable)....although it wouldnt be hard for someone that
              downloads
              > > it to manually add them, just sayin.....
              > >
              >
              >
              >
              >
              >
              >
              >
              > But of course. The focus of this release was preserving the major
              tabular
              > data that could be extracted in an essentially automated way.
              >
              > Of course, if anyone were to fill in the affiliations in a new
              column and
              > send them along, they'd get included even faster than if I have to
              do it
              > myself!
              >
              > TT
              > --
              > drarbiter@... - AMDG
              >
            • Theodore Turocy
              Hi again all (and apologies for crossposting): Following up on my initial compilation of 2006 minor league statistics, I have also added a parallel set of
              Message 6 of 20 , Jan 28, 2007
              • 0 Attachment
                Hi again all (and apologies for crossposting):

                Following up on my initial compilation of 2006 minor league
                statistics, I have also added a parallel set of statistics from 2005.

                These can be gotten from

                http://econweb.tamu.edu/turocy/minors

                I have also updated 2006 with the affiliations posted to
                baseball-databank, as well as fixing a couple errors.

                I do not have any plans to pursue 2004. As I believe is fairly
                well-known by now, the published statistics for 2004 have significant
                problems. I know many people have been working on locating and making
                public more correct versions. So it seems to make sense for me to
                defer to them on that.

                I want to avoid spamming these lists too much, so I won't announce
                future minor revisions; interested types should check the webpage from
                time to time to see if there are updates. I do anticipate adding some
                columns to both seasons in the near future, time permitting.

                TT
                --
                drarbiter@... - AMDG
              • William
                Is there a MySQL equivalent to this query? I m struggling with converting it from Access to Mysql.
                Message 7 of 20 , May 3, 2009
                • 0 Attachment
                  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
                  >
                • Tangotiger
                  Can you show us what functions you ve tried? Here s a related one in Oracle I ve used (for something else): substr(min(to_char(order_id + 90000, 00000 ) ||
                  Message 8 of 20 , May 8, 2009
                  • 0 Attachment
                    Can you show us what functions you've tried?

                    Here's a related one in Oracle I've used (for something else):
                    substr(min(to_char(order_id + 90000,'00000') ||
                    to_char(mlbam_id,'000000')),8,6) as best_mlbam_id

                    See if the idea there helps any.

                    Tom



                    > 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
                    >>
                    >
                    >
                    >


                    ---------------------------------------------
                    The Book--Playing The Percentages In Baseball
                    http://www.InsideTheBook.com
                  • Bryan Walko
                    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,
                    Message 9 of 20 , May 8, 2009
                    • 0 Attachment
                      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
                      >


                    • William
                      I just figured it out: SELECT f2.yearid, f2.playerid, MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)), _ ,f2.pos)),6) AS PrimPos, Max(f2.GP) AS GP_Pos, SUM(f2.GP)
                      Message 10 of 20 , May 8, 2009
                      • 0 Attachment
                        I just figured it out:

                        SELECT
                        f2.yearid,
                        f2.playerid,
                        MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS PrimPos,
                        Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                        IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6)) AS LastPos
                        FROM
                        (SELECT
                        yearid,
                        playerid,
                        pos,
                        SUM(g) as GP
                        from fielding
                        group by yearid, playerid, pos) AS f2
                        GROUP BY f2.yearid, f2.playerid

                        --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                        >
                        > Can you show us what functions you've tried?
                        >
                        > Here's a related one in Oracle I've used (for something else):
                        > substr(min(to_char(order_id + 90000,'00000') ||
                        > to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                        >
                        > See if the idea there helps any.
                        >
                        > Tom
                        >
                        >
                        >
                        > > 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
                        > >>
                        > >
                        > >
                        > >
                        >
                        >
                        > ---------------------------------------------
                        > The Book--Playing The Percentages In Baseball
                        > http://www.InsideTheBook.com
                        >
                      • Tangotiger
                        I m surprised it works as you ve laid it out (which is not to say that it s wrong, since I presume you ve tested it). The reason I put 1000 + GP , is so that
                        Message 11 of 20 , May 11, 2009
                        • 0 Attachment
                          I'm surprised it works as you've laid it out (which is not to say that
                          it's wrong, since I presume you've tested it).

                          The reason I put "1000 + GP", is so that I'm guaranteed to have a 4-digit
                          number. In your case then, it would be clearer to turn this:
                          CONCAT(1000 + CAST(f2.GP as CHAR(3))

                          Into this:
                          CONCAT(CAST(1000 + f2.GP as CHAR(4))

                          In your version, what would a number added to a variable that is numeric
                          but been cast as a string return?

                          Furthermore, if you can cast something to force three characters (I
                          presume casting 1 GP in CHAR3 would return 001 ?), then there's no point
                          to even adding in the 1000 to begin with.

                          I don't do much MySQL, so anything more that you can show us would be
                          welcomed.

                          Thanks, Tom




                          > I just figured it out:
                          >
                          > SELECT
                          > f2.yearid,
                          > f2.playerid,
                          > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS PrimPos,
                          > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                          > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                          > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                          > FROM
                          > (SELECT
                          > yearid,
                          > playerid,
                          > pos,
                          > SUM(g) as GP
                          > from fielding
                          > group by yearid, playerid, pos) AS f2
                          > GROUP BY f2.yearid, f2.playerid
                          >
                          > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                          >>
                          >> Can you show us what functions you've tried?
                          >>
                          >> Here's a related one in Oracle I've used (for something else):
                          >> substr(min(to_char(order_id + 90000,'00000') ||
                          >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                          >>
                          >> See if the idea there helps any.
                          >>
                          >> Tom
                          >>
                          >>
                          >>
                          >> > 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
                          >> >>
                          >> >
                          >> >
                          >> >
                          >>
                          >>
                          >> ---------------------------------------------
                          >> The Book--Playing The Percentages In Baseball
                          >> http://www.InsideTheBook.com
                          >>
                          >
                          >
                          >


                          ---------------------------------------------
                          The Book--Playing The Percentages In Baseball
                          http://www.InsideTheBook.com
                        • Tangotiger
                          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
                          Message 12 of 20 , May 11, 2009
                          • 0 Attachment
                            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
                          • William
                            I used CHAR(3) because the G column in the fielding table is smallint(3) in MySQL. I learned that I can actually change it to CAST(g as CHAR) and get the same
                            Message 13 of 20 , May 11, 2009
                            • 0 Attachment
                              I used CHAR(3) because the G column in the fielding table is smallint(3) in MySQL. I learned that I can actually change it to CAST(g as CHAR) and get the same results.


                              If I run this query:

                              SELECT
                              yearid,
                              playerid,
                              MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                              FROM fielding WHERE playerID = 'ruthba01'
                              GROUP BY yearid, playerid

                              I get these results:

                              yearid,playerid,PrimPos
                              1914,ruthba01,1004_P
                              1915,ruthba01,1032_P
                              1916,ruthba01,1044_P
                              1917,ruthba01,1041_P
                              1918,ruthba01,1059_OF
                              1919,ruthba01,1111_OF
                              1920,ruthba01,1141_OF
                              1921,ruthba01,1152_OF
                              1922,ruthba01,1110_OF
                              1923,ruthba01,1148_OF
                              1924,ruthba01,1152_OF
                              1925,ruthba01,1098_OF
                              1926,ruthba01,1149_OF
                              1927,ruthba01,1151_OF
                              1928,ruthba01,1154_OF
                              1929,ruthba01,1133_OF
                              1930,ruthba01,1144_OF
                              1931,ruthba01,1142_OF
                              1932,ruthba01,1128_OF
                              1933,ruthba01,1132_OF
                              1934,ruthba01,1111_OF
                              1935,ruthba01,1026_OF

                              --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                              >
                              > I'm surprised it works as you've laid it out (which is not to say that
                              > it's wrong, since I presume you've tested it).
                              >
                              > The reason I put "1000 + GP", is so that I'm guaranteed to have a 4-digit
                              > number. In your case then, it would be clearer to turn this:
                              > CONCAT(1000 + CAST(f2.GP as CHAR(3))
                              >
                              > Into this:
                              > CONCAT(CAST(1000 + f2.GP as CHAR(4))
                              >
                              > In your version, what would a number added to a variable that is numeric
                              > but been cast as a string return?
                              >
                              > Furthermore, if you can cast something to force three characters (I
                              > presume casting 1 GP in CHAR3 would return 001 ?), then there's no point
                              > to even adding in the 1000 to begin with.
                              >
                              > I don't do much MySQL, so anything more that you can show us would be
                              > welcomed.
                              >
                              > Thanks, Tom
                              >
                              >
                              >
                              >
                              > > I just figured it out:
                              > >
                              > > SELECT
                              > > f2.yearid,
                              > > f2.playerid,
                              > > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS PrimPos,
                              > > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                              > > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                              > > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                              > > FROM
                              > > (SELECT
                              > > yearid,
                              > > playerid,
                              > > pos,
                              > > SUM(g) as GP
                              > > from fielding
                              > > group by yearid, playerid, pos) AS f2
                              > > GROUP BY f2.yearid, f2.playerid
                              > >
                              > > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                              > >>
                              > >> Can you show us what functions you've tried?
                              > >>
                              > >> Here's a related one in Oracle I've used (for something else):
                              > >> substr(min(to_char(order_id + 90000,'00000') ||
                              > >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                              > >>
                              > >> See if the idea there helps any.
                              > >>
                              > >> Tom
                              > >>
                              > >>
                              > >>
                              > >> > 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
                              > >> >>
                              > >> >
                              > >> >
                              > >> >
                              > >>
                              > >>
                              > >> ---------------------------------------------
                              > >> The Book--Playing The Percentages In Baseball
                              > >> http://www.InsideTheBook.com
                              > >>
                              > >
                              > >
                              > >
                              >
                              >
                              > ---------------------------------------------
                              > The Book--Playing The Percentages In Baseball
                              > http://www.InsideTheBook.com
                              >
                            • Tangotiger
                              What happens if you change: MAX(CONCAT(1000 + CAST(g as CHAR), _ ,pos)) AS PrimPos To: MAX(CONCAT(CAST(g as CHAR), _ ,pos)) AS PrimPos Tom ... The
                              Message 14 of 20 , May 11, 2009
                              • 0 Attachment
                                What happens if you change:
                                MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos

                                To:
                                MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS PrimPos

                                Tom

                                > I used CHAR(3) because the G column in the fielding table is smallint(3)
                                > in MySQL. I learned that I can actually change it to CAST(g as CHAR) and
                                > get the same results.
                                >
                                >
                                > If I run this query:
                                >
                                > SELECT
                                > yearid,
                                > playerid,
                                > MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                > FROM fielding WHERE playerID = 'ruthba01'
                                > GROUP BY yearid, playerid
                                >
                                > I get these results:
                                >
                                > yearid,playerid,PrimPos
                                > 1914,ruthba01,1004_P
                                > 1915,ruthba01,1032_P
                                > 1916,ruthba01,1044_P
                                > 1917,ruthba01,1041_P
                                > 1918,ruthba01,1059_OF
                                > 1919,ruthba01,1111_OF
                                > 1920,ruthba01,1141_OF
                                > 1921,ruthba01,1152_OF
                                > 1922,ruthba01,1110_OF
                                > 1923,ruthba01,1148_OF
                                > 1924,ruthba01,1152_OF
                                > 1925,ruthba01,1098_OF
                                > 1926,ruthba01,1149_OF
                                > 1927,ruthba01,1151_OF
                                > 1928,ruthba01,1154_OF
                                > 1929,ruthba01,1133_OF
                                > 1930,ruthba01,1144_OF
                                > 1931,ruthba01,1142_OF
                                > 1932,ruthba01,1128_OF
                                > 1933,ruthba01,1132_OF
                                > 1934,ruthba01,1111_OF
                                > 1935,ruthba01,1026_OF
                                >
                                > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                                >>
                                >> I'm surprised it works as you've laid it out (which is not to say that
                                >> it's wrong, since I presume you've tested it).
                                >>
                                >> The reason I put "1000 + GP", is so that I'm guaranteed to have a
                                >> 4-digit
                                >> number. In your case then, it would be clearer to turn this:
                                >> CONCAT(1000 + CAST(f2.GP as CHAR(3))
                                >>
                                >> Into this:
                                >> CONCAT(CAST(1000 + f2.GP as CHAR(4))
                                >>
                                >> In your version, what would a number added to a variable that is numeric
                                >> but been cast as a string return?
                                >>
                                >> Furthermore, if you can cast something to force three characters (I
                                >> presume casting 1 GP in CHAR3 would return 001 ?), then there's no point
                                >> to even adding in the 1000 to begin with.
                                >>
                                >> I don't do much MySQL, so anything more that you can show us would be
                                >> welcomed.
                                >>
                                >> Thanks, Tom
                                >>
                                >>
                                >>
                                >>
                                >> > I just figured it out:
                                >> >
                                >> > SELECT
                                >> > f2.yearid,
                                >> > f2.playerid,
                                >> > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS
                                >> PrimPos,
                                >> > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                                >> > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                                >> > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                                >> > FROM
                                >> > (SELECT
                                >> > yearid,
                                >> > playerid,
                                >> > pos,
                                >> > SUM(g) as GP
                                >> > from fielding
                                >> > group by yearid, playerid, pos) AS f2
                                >> > GROUP BY f2.yearid, f2.playerid
                                >> >
                                >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                >> >>
                                >> >> Can you show us what functions you've tried?
                                >> >>
                                >> >> Here's a related one in Oracle I've used (for something else):
                                >> >> substr(min(to_char(order_id + 90000,'00000') ||
                                >> >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                                >> >>
                                >> >> See if the idea there helps any.
                                >> >>
                                >> >> Tom
                                >> >>
                                >> >>
                                >> >>
                                >> >> > 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
                                >> >> >>
                                >> >> >
                                >> >> >
                                >> >> >
                                >> >>
                                >> >>
                                >> >> ---------------------------------------------
                                >> >> The Book--Playing The Percentages In Baseball
                                >> >> http://www.InsideTheBook.com
                                >> >>
                                >> >
                                >> >
                                >> >
                                >>
                                >>
                                >> ---------------------------------------------
                                >> The Book--Playing The Percentages In Baseball
                                >> http://www.InsideTheBook.com
                                >>
                                >
                                >
                                >


                                ---------------------------------------------
                                The Book--Playing The Percentages In Baseball
                                http://www.InsideTheBook.com
                              • William
                                These are the results if I use MAX(CONCAT(CAST(g as CHAR), _ ,pos)) AS PrimPos yearid,playerid,PrimPos 1914,ruthba01,4_P 1915,ruthba01,32_P 1916,ruthba01,44_P
                                Message 15 of 20 , May 11, 2009
                                • 0 Attachment
                                  These are the results if I use MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS PrimPos

                                  yearid,playerid,PrimPos
                                  1914,ruthba01,4_P
                                  1915,ruthba01,32_P
                                  1916,ruthba01,44_P
                                  1917,ruthba01,41_P
                                  1918,ruthba01,59_OF
                                  1919,ruthba01,5_1B
                                  1920,ruthba01,2_1B
                                  1921,ruthba01,2_P
                                  1922,ruthba01,1_1B
                                  1923,ruthba01,4_1B
                                  1924,ruthba01,152_OF
                                  1925,ruthba01,98_OF
                                  1926,ruthba01,2_1B
                                  1927,ruthba01,151_OF
                                  1928,ruthba01,154_OF
                                  1929,ruthba01,133_OF
                                  1930,ruthba01,1_P
                                  1931,ruthba01,1_1B
                                  1932,ruthba01,1_1B
                                  1933,ruthba01,1_P
                                  1934,ruthba01,111_OF
                                  1935,ruthba01,26_OF

                                  --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                                  >
                                  > What happens if you change:
                                  > MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                  >
                                  > To:
                                  > MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS PrimPos
                                  >
                                  > Tom
                                  >
                                  > > I used CHAR(3) because the G column in the fielding table is smallint(3)
                                  > > in MySQL. I learned that I can actually change it to CAST(g as CHAR) and
                                  > > get the same results.
                                  > >
                                  > >
                                  > > If I run this query:
                                  > >
                                  > > SELECT
                                  > > yearid,
                                  > > playerid,
                                  > > MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                  > > FROM fielding WHERE playerID = 'ruthba01'
                                  > > GROUP BY yearid, playerid
                                  > >
                                  > > I get these results:
                                  > >
                                  > > yearid,playerid,PrimPos
                                  > > 1914,ruthba01,1004_P
                                  > > 1915,ruthba01,1032_P
                                  > > 1916,ruthba01,1044_P
                                  > > 1917,ruthba01,1041_P
                                  > > 1918,ruthba01,1059_OF
                                  > > 1919,ruthba01,1111_OF
                                  > > 1920,ruthba01,1141_OF
                                  > > 1921,ruthba01,1152_OF
                                  > > 1922,ruthba01,1110_OF
                                  > > 1923,ruthba01,1148_OF
                                  > > 1924,ruthba01,1152_OF
                                  > > 1925,ruthba01,1098_OF
                                  > > 1926,ruthba01,1149_OF
                                  > > 1927,ruthba01,1151_OF
                                  > > 1928,ruthba01,1154_OF
                                  > > 1929,ruthba01,1133_OF
                                  > > 1930,ruthba01,1144_OF
                                  > > 1931,ruthba01,1142_OF
                                  > > 1932,ruthba01,1128_OF
                                  > > 1933,ruthba01,1132_OF
                                  > > 1934,ruthba01,1111_OF
                                  > > 1935,ruthba01,1026_OF
                                  > >
                                  > > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                  > >>
                                  > >> I'm surprised it works as you've laid it out (which is not to say that
                                  > >> it's wrong, since I presume you've tested it).
                                  > >>
                                  > >> The reason I put "1000 + GP", is so that I'm guaranteed to have a
                                  > >> 4-digit
                                  > >> number. In your case then, it would be clearer to turn this:
                                  > >> CONCAT(1000 + CAST(f2.GP as CHAR(3))
                                  > >>
                                  > >> Into this:
                                  > >> CONCAT(CAST(1000 + f2.GP as CHAR(4))
                                  > >>
                                  > >> In your version, what would a number added to a variable that is numeric
                                  > >> but been cast as a string return?
                                  > >>
                                  > >> Furthermore, if you can cast something to force three characters (I
                                  > >> presume casting 1 GP in CHAR3 would return 001 ?), then there's no point
                                  > >> to even adding in the 1000 to begin with.
                                  > >>
                                  > >> I don't do much MySQL, so anything more that you can show us would be
                                  > >> welcomed.
                                  > >>
                                  > >> Thanks, Tom
                                  > >>
                                  > >>
                                  > >>
                                  > >>
                                  > >> > I just figured it out:
                                  > >> >
                                  > >> > SELECT
                                  > >> > f2.yearid,
                                  > >> > f2.playerid,
                                  > >> > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS
                                  > >> PrimPos,
                                  > >> > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                                  > >> > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                                  > >> > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                                  > >> > FROM
                                  > >> > (SELECT
                                  > >> > yearid,
                                  > >> > playerid,
                                  > >> > pos,
                                  > >> > SUM(g) as GP
                                  > >> > from fielding
                                  > >> > group by yearid, playerid, pos) AS f2
                                  > >> > GROUP BY f2.yearid, f2.playerid
                                  > >> >
                                  > >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                  > >> >>
                                  > >> >> Can you show us what functions you've tried?
                                  > >> >>
                                  > >> >> Here's a related one in Oracle I've used (for something else):
                                  > >> >> substr(min(to_char(order_id + 90000,'00000') ||
                                  > >> >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                                  > >> >>
                                  > >> >> See if the idea there helps any.
                                  > >> >>
                                  > >> >> Tom
                                  > >> >>
                                  > >> >>
                                  > >> >>
                                  > >> >> > 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
                                  > >> >> >>
                                  > >> >> >
                                  > >> >> >
                                  > >> >> >
                                  > >> >>
                                  > >> >>
                                  > >> >> ---------------------------------------------
                                  > >> >> The Book--Playing The Percentages In Baseball
                                  > >> >> http://www.InsideTheBook.com
                                  > >> >>
                                  > >> >
                                  > >> >
                                  > >> >
                                  > >>
                                  > >>
                                  > >> ---------------------------------------------
                                  > >> The Book--Playing The Percentages In Baseball
                                  > >> http://www.InsideTheBook.com
                                  > >>
                                  > >
                                  > >
                                  > >
                                  >
                                  >
                                  > ---------------------------------------------
                                  > The Book--Playing The Percentages In Baseball
                                  > http://www.InsideTheBook.com
                                  >
                                • Tangotiger
                                  Ah-ha, so CHAR does not use any particular property. If you do this: CAST(g as CHAR(3) Then do you get the desired results? Tom ... The Book--Playing The
                                  Message 16 of 20 , May 13, 2009
                                  • 0 Attachment
                                    Ah-ha, so CHAR does not use any particular property. If you do this:
                                    CAST(g as CHAR(3)

                                    Then do you get the desired results?

                                    Tom

                                    > These are the results if I use MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS
                                    > PrimPos
                                    >
                                    > yearid,playerid,PrimPos
                                    > 1914,ruthba01,4_P
                                    > 1915,ruthba01,32_P
                                    > 1916,ruthba01,44_P
                                    > 1917,ruthba01,41_P
                                    > 1918,ruthba01,59_OF
                                    > 1919,ruthba01,5_1B
                                    > 1920,ruthba01,2_1B
                                    > 1921,ruthba01,2_P
                                    > 1922,ruthba01,1_1B
                                    > 1923,ruthba01,4_1B
                                    > 1924,ruthba01,152_OF
                                    > 1925,ruthba01,98_OF
                                    > 1926,ruthba01,2_1B
                                    > 1927,ruthba01,151_OF
                                    > 1928,ruthba01,154_OF
                                    > 1929,ruthba01,133_OF
                                    > 1930,ruthba01,1_P
                                    > 1931,ruthba01,1_1B
                                    > 1932,ruthba01,1_1B
                                    > 1933,ruthba01,1_P
                                    > 1934,ruthba01,111_OF
                                    > 1935,ruthba01,26_OF
                                    >
                                    > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                                    >>
                                    >> What happens if you change:
                                    >> MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                    >>
                                    >> To:
                                    >> MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS PrimPos
                                    >>
                                    >> Tom
                                    >>
                                    >> > I used CHAR(3) because the G column in the fielding table is
                                    >> smallint(3)
                                    >> > in MySQL. I learned that I can actually change it to CAST(g as CHAR)
                                    >> and
                                    >> > get the same results.
                                    >> >
                                    >> >
                                    >> > If I run this query:
                                    >> >
                                    >> > SELECT
                                    >> > yearid,
                                    >> > playerid,
                                    >> > MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                    >> > FROM fielding WHERE playerID = 'ruthba01'
                                    >> > GROUP BY yearid, playerid
                                    >> >
                                    >> > I get these results:
                                    >> >
                                    >> > yearid,playerid,PrimPos
                                    >> > 1914,ruthba01,1004_P
                                    >> > 1915,ruthba01,1032_P
                                    >> > 1916,ruthba01,1044_P
                                    >> > 1917,ruthba01,1041_P
                                    >> > 1918,ruthba01,1059_OF
                                    >> > 1919,ruthba01,1111_OF
                                    >> > 1920,ruthba01,1141_OF
                                    >> > 1921,ruthba01,1152_OF
                                    >> > 1922,ruthba01,1110_OF
                                    >> > 1923,ruthba01,1148_OF
                                    >> > 1924,ruthba01,1152_OF
                                    >> > 1925,ruthba01,1098_OF
                                    >> > 1926,ruthba01,1149_OF
                                    >> > 1927,ruthba01,1151_OF
                                    >> > 1928,ruthba01,1154_OF
                                    >> > 1929,ruthba01,1133_OF
                                    >> > 1930,ruthba01,1144_OF
                                    >> > 1931,ruthba01,1142_OF
                                    >> > 1932,ruthba01,1128_OF
                                    >> > 1933,ruthba01,1132_OF
                                    >> > 1934,ruthba01,1111_OF
                                    >> > 1935,ruthba01,1026_OF
                                    >> >
                                    >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                    >> >>
                                    >> >> I'm surprised it works as you've laid it out (which is not to say
                                    >> that
                                    >> >> it's wrong, since I presume you've tested it).
                                    >> >>
                                    >> >> The reason I put "1000 + GP", is so that I'm guaranteed to have a
                                    >> >> 4-digit
                                    >> >> number. In your case then, it would be clearer to turn this:
                                    >> >> CONCAT(1000 + CAST(f2.GP as CHAR(3))
                                    >> >>
                                    >> >> Into this:
                                    >> >> CONCAT(CAST(1000 + f2.GP as CHAR(4))
                                    >> >>
                                    >> >> In your version, what would a number added to a variable that is
                                    >> numeric
                                    >> >> but been cast as a string return?
                                    >> >>
                                    >> >> Furthermore, if you can cast something to force three characters (I
                                    >> >> presume casting 1 GP in CHAR3 would return 001 ?), then there's no
                                    >> point
                                    >> >> to even adding in the 1000 to begin with.
                                    >> >>
                                    >> >> I don't do much MySQL, so anything more that you can show us would be
                                    >> >> welcomed.
                                    >> >>
                                    >> >> Thanks, Tom
                                    >> >>
                                    >> >>
                                    >> >>
                                    >> >>
                                    >> >> > I just figured it out:
                                    >> >> >
                                    >> >> > SELECT
                                    >> >> > f2.yearid,
                                    >> >> > f2.playerid,
                                    >> >> > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS
                                    >> >> PrimPos,
                                    >> >> > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                                    >> >> > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                                    >> >> > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                                    >> >> > FROM
                                    >> >> > (SELECT
                                    >> >> > yearid,
                                    >> >> > playerid,
                                    >> >> > pos,
                                    >> >> > SUM(g) as GP
                                    >> >> > from fielding
                                    >> >> > group by yearid, playerid, pos) AS f2
                                    >> >> > GROUP BY f2.yearid, f2.playerid
                                    >> >> >
                                    >> >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@>
                                    >> wrote:
                                    >> >> >>
                                    >> >> >> Can you show us what functions you've tried?
                                    >> >> >>
                                    >> >> >> Here's a related one in Oracle I've used (for something else):
                                    >> >> >> substr(min(to_char(order_id + 90000,'00000') ||
                                    >> >> >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                                    >> >> >>
                                    >> >> >> See if the idea there helps any.
                                    >> >> >>
                                    >> >> >> Tom
                                    >> >> >>
                                    >> >> >>
                                    >> >> >>
                                    >> >> >> > 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
                                    >> >> >> >>
                                    >> >> >> >
                                    >> >> >> >
                                    >> >> >> >
                                    >> >> >>
                                    >> >> >>
                                    >> >> >> ---------------------------------------------
                                    >> >> >> The Book--Playing The Percentages In Baseball
                                    >> >> >> http://www.InsideTheBook.com
                                    >> >> >>
                                    >> >> >
                                    >> >> >
                                    >> >> >
                                    >> >>
                                    >> >>
                                    >> >> ---------------------------------------------
                                    >> >> The Book--Playing The Percentages In Baseball
                                    >> >> http://www.InsideTheBook.com
                                    >> >>
                                    >> >
                                    >> >
                                    >> >
                                    >>
                                    >>
                                    >> ---------------------------------------------
                                    >> The Book--Playing The Percentages In Baseball
                                    >> http://www.InsideTheBook.com
                                    >>
                                    >
                                    >
                                    >


                                    ---------------------------------------------
                                    The Book--Playing The Percentages In Baseball
                                    http://www.InsideTheBook.com
                                  • William
                                    No, I get the same results as before. CAST() seems to behave the same way Str() does for Access. From dev.mysql.com: The CAST() function takes a value of one
                                    Message 17 of 20 , May 13, 2009
                                    • 0 Attachment
                                      No, I get the same results as before. CAST() seems to behave the same way Str() does for Access.

                                      From dev.mysql.com:
                                      The CAST() function takes a value of one type and produce a value of another type

                                      --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                                      >
                                      > Ah-ha, so CHAR does not use any particular property. If you do this:
                                      > CAST(g as CHAR(3)
                                      >
                                      > Then do you get the desired results?
                                      >
                                      > Tom
                                      >
                                      > > These are the results if I use MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS
                                      > > PrimPos
                                      > >
                                      > > yearid,playerid,PrimPos
                                      > > 1914,ruthba01,4_P
                                      > > 1915,ruthba01,32_P
                                      > > 1916,ruthba01,44_P
                                      > > 1917,ruthba01,41_P
                                      > > 1918,ruthba01,59_OF
                                      > > 1919,ruthba01,5_1B
                                      > > 1920,ruthba01,2_1B
                                      > > 1921,ruthba01,2_P
                                      > > 1922,ruthba01,1_1B
                                      > > 1923,ruthba01,4_1B
                                      > > 1924,ruthba01,152_OF
                                      > > 1925,ruthba01,98_OF
                                      > > 1926,ruthba01,2_1B
                                      > > 1927,ruthba01,151_OF
                                      > > 1928,ruthba01,154_OF
                                      > > 1929,ruthba01,133_OF
                                      > > 1930,ruthba01,1_P
                                      > > 1931,ruthba01,1_1B
                                      > > 1932,ruthba01,1_1B
                                      > > 1933,ruthba01,1_P
                                      > > 1934,ruthba01,111_OF
                                      > > 1935,ruthba01,26_OF
                                      > >
                                      > > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                      > >>
                                      > >> What happens if you change:
                                      > >> MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                      > >>
                                      > >> To:
                                      > >> MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS PrimPos
                                      > >>
                                      > >> Tom
                                      > >>
                                      > >> > I used CHAR(3) because the G column in the fielding table is
                                      > >> smallint(3)
                                      > >> > in MySQL. I learned that I can actually change it to CAST(g as CHAR)
                                      > >> and
                                      > >> > get the same results.
                                      > >> >
                                      > >> >
                                      > >> > If I run this query:
                                      > >> >
                                      > >> > SELECT
                                      > >> > yearid,
                                      > >> > playerid,
                                      > >> > MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                      > >> > FROM fielding WHERE playerID = 'ruthba01'
                                      > >> > GROUP BY yearid, playerid
                                      > >> >
                                      > >> > I get these results:
                                      > >> >
                                      > >> > yearid,playerid,PrimPos
                                      > >> > 1914,ruthba01,1004_P
                                      > >> > 1915,ruthba01,1032_P
                                      > >> > 1916,ruthba01,1044_P
                                      > >> > 1917,ruthba01,1041_P
                                      > >> > 1918,ruthba01,1059_OF
                                      > >> > 1919,ruthba01,1111_OF
                                      > >> > 1920,ruthba01,1141_OF
                                      > >> > 1921,ruthba01,1152_OF
                                      > >> > 1922,ruthba01,1110_OF
                                      > >> > 1923,ruthba01,1148_OF
                                      > >> > 1924,ruthba01,1152_OF
                                      > >> > 1925,ruthba01,1098_OF
                                      > >> > 1926,ruthba01,1149_OF
                                      > >> > 1927,ruthba01,1151_OF
                                      > >> > 1928,ruthba01,1154_OF
                                      > >> > 1929,ruthba01,1133_OF
                                      > >> > 1930,ruthba01,1144_OF
                                      > >> > 1931,ruthba01,1142_OF
                                      > >> > 1932,ruthba01,1128_OF
                                      > >> > 1933,ruthba01,1132_OF
                                      > >> > 1934,ruthba01,1111_OF
                                      > >> > 1935,ruthba01,1026_OF
                                      > >> >
                                      > >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                      > >> >>
                                      > >> >> I'm surprised it works as you've laid it out (which is not to say
                                      > >> that
                                      > >> >> it's wrong, since I presume you've tested it).
                                      > >> >>
                                      > >> >> The reason I put "1000 + GP", is so that I'm guaranteed to have a
                                      > >> >> 4-digit
                                      > >> >> number. In your case then, it would be clearer to turn this:
                                      > >> >> CONCAT(1000 + CAST(f2.GP as CHAR(3))
                                      > >> >>
                                      > >> >> Into this:
                                      > >> >> CONCAT(CAST(1000 + f2.GP as CHAR(4))
                                      > >> >>
                                      > >> >> In your version, what would a number added to a variable that is
                                      > >> numeric
                                      > >> >> but been cast as a string return?
                                      > >> >>
                                      > >> >> Furthermore, if you can cast something to force three characters (I
                                      > >> >> presume casting 1 GP in CHAR3 would return 001 ?), then there's no
                                      > >> point
                                      > >> >> to even adding in the 1000 to begin with.
                                      > >> >>
                                      > >> >> I don't do much MySQL, so anything more that you can show us would be
                                      > >> >> welcomed.
                                      > >> >>
                                      > >> >> Thanks, Tom
                                      > >> >>
                                      > >> >>
                                      > >> >>
                                      > >> >>
                                      > >> >> > I just figured it out:
                                      > >> >> >
                                      > >> >> > SELECT
                                      > >> >> > f2.yearid,
                                      > >> >> > f2.playerid,
                                      > >> >> > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS
                                      > >> >> PrimPos,
                                      > >> >> > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                                      > >> >> > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                                      > >> >> > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                                      > >> >> > FROM
                                      > >> >> > (SELECT
                                      > >> >> > yearid,
                                      > >> >> > playerid,
                                      > >> >> > pos,
                                      > >> >> > SUM(g) as GP
                                      > >> >> > from fielding
                                      > >> >> > group by yearid, playerid, pos) AS f2
                                      > >> >> > GROUP BY f2.yearid, f2.playerid
                                      > >> >> >
                                      > >> >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@>
                                      > >> wrote:
                                      > >> >> >>
                                      > >> >> >> Can you show us what functions you've tried?
                                      > >> >> >>
                                      > >> >> >> Here's a related one in Oracle I've used (for something else):
                                      > >> >> >> substr(min(to_char(order_id + 90000,'00000') ||
                                      > >> >> >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                                      > >> >> >>
                                      > >> >> >> See if the idea there helps any.
                                      > >> >> >>
                                      > >> >> >> Tom
                                      > >> >> >>
                                      > >> >> >>
                                      > >> >> >>
                                      > >> >> >> > 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
                                      > >> >> >> >>
                                      > >> >> >> >
                                      > >> >> >> >
                                      > >> >> >> >
                                      > >> >> >>
                                      > >> >> >>
                                      > >> >> >> ---------------------------------------------
                                      > >> >> >> The Book--Playing The Percentages In Baseball
                                      > >> >> >> http://www.InsideTheBook.com
                                      > >> >> >>
                                      > >> >> >
                                      > >> >> >
                                      > >> >> >
                                      > >> >>
                                      > >> >>
                                      > >> >> ---------------------------------------------
                                      > >> >> The Book--Playing The Percentages In Baseball
                                      > >> >> http://www.InsideTheBook.com
                                      > >> >>
                                      > >> >
                                      > >> >
                                      > >> >
                                      > >>
                                      > >>
                                      > >> ---------------------------------------------
                                      > >> The Book--Playing The Percentages In Baseball
                                      > >> http://www.InsideTheBook.com
                                      > >>
                                      > >
                                      > >
                                      > >
                                      >
                                      >
                                      > ---------------------------------------------
                                      > The Book--Playing The Percentages In Baseball
                                      > http://www.InsideTheBook.com
                                      >
                                    • Tangotiger
                                      Great, thanks. Ok, so the cleanest, correct method would be to do: CAST(1000+g as CHAR) Tom ... The Book--Playing The Percentages In Baseball
                                      Message 18 of 20 , May 14, 2009
                                      • 0 Attachment
                                        Great, thanks. Ok, so the cleanest, correct method would be to do:
                                        CAST(1000+g as CHAR)

                                        Tom


                                        > No, I get the same results as before. CAST() seems to behave the same way
                                        > Str() does for Access.
                                        >
                                        > From dev.mysql.com:
                                        > The CAST() function takes a value of one type and produce a value of
                                        > another type
                                        >
                                        > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@...> wrote:
                                        >>
                                        >> Ah-ha, so CHAR does not use any particular property. If you do this:
                                        >> CAST(g as CHAR(3)
                                        >>
                                        >> Then do you get the desired results?
                                        >>
                                        >> Tom
                                        >>
                                        >> > These are the results if I use MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS
                                        >> > PrimPos
                                        >> >
                                        >> > yearid,playerid,PrimPos
                                        >> > 1914,ruthba01,4_P
                                        >> > 1915,ruthba01,32_P
                                        >> > 1916,ruthba01,44_P
                                        >> > 1917,ruthba01,41_P
                                        >> > 1918,ruthba01,59_OF
                                        >> > 1919,ruthba01,5_1B
                                        >> > 1920,ruthba01,2_1B
                                        >> > 1921,ruthba01,2_P
                                        >> > 1922,ruthba01,1_1B
                                        >> > 1923,ruthba01,4_1B
                                        >> > 1924,ruthba01,152_OF
                                        >> > 1925,ruthba01,98_OF
                                        >> > 1926,ruthba01,2_1B
                                        >> > 1927,ruthba01,151_OF
                                        >> > 1928,ruthba01,154_OF
                                        >> > 1929,ruthba01,133_OF
                                        >> > 1930,ruthba01,1_P
                                        >> > 1931,ruthba01,1_1B
                                        >> > 1932,ruthba01,1_1B
                                        >> > 1933,ruthba01,1_P
                                        >> > 1934,ruthba01,111_OF
                                        >> > 1935,ruthba01,26_OF
                                        >> >
                                        >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@> wrote:
                                        >> >>
                                        >> >> What happens if you change:
                                        >> >> MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                        >> >>
                                        >> >> To:
                                        >> >> MAX(CONCAT(CAST(g as CHAR),"_",pos)) AS PrimPos
                                        >> >>
                                        >> >> Tom
                                        >> >>
                                        >> >> > I used CHAR(3) because the G column in the fielding table is
                                        >> >> smallint(3)
                                        >> >> > in MySQL. I learned that I can actually change it to CAST(g as
                                        >> CHAR)
                                        >> >> and
                                        >> >> > get the same results.
                                        >> >> >
                                        >> >> >
                                        >> >> > If I run this query:
                                        >> >> >
                                        >> >> > SELECT
                                        >> >> > yearid,
                                        >> >> > playerid,
                                        >> >> > MAX(CONCAT(1000 + CAST(g as CHAR),"_",pos)) AS PrimPos
                                        >> >> > FROM fielding WHERE playerID = 'ruthba01'
                                        >> >> > GROUP BY yearid, playerid
                                        >> >> >
                                        >> >> > I get these results:
                                        >> >> >
                                        >> >> > yearid,playerid,PrimPos
                                        >> >> > 1914,ruthba01,1004_P
                                        >> >> > 1915,ruthba01,1032_P
                                        >> >> > 1916,ruthba01,1044_P
                                        >> >> > 1917,ruthba01,1041_P
                                        >> >> > 1918,ruthba01,1059_OF
                                        >> >> > 1919,ruthba01,1111_OF
                                        >> >> > 1920,ruthba01,1141_OF
                                        >> >> > 1921,ruthba01,1152_OF
                                        >> >> > 1922,ruthba01,1110_OF
                                        >> >> > 1923,ruthba01,1148_OF
                                        >> >> > 1924,ruthba01,1152_OF
                                        >> >> > 1925,ruthba01,1098_OF
                                        >> >> > 1926,ruthba01,1149_OF
                                        >> >> > 1927,ruthba01,1151_OF
                                        >> >> > 1928,ruthba01,1154_OF
                                        >> >> > 1929,ruthba01,1133_OF
                                        >> >> > 1930,ruthba01,1144_OF
                                        >> >> > 1931,ruthba01,1142_OF
                                        >> >> > 1932,ruthba01,1128_OF
                                        >> >> > 1933,ruthba01,1132_OF
                                        >> >> > 1934,ruthba01,1111_OF
                                        >> >> > 1935,ruthba01,1026_OF
                                        >> >> >
                                        >> >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@>
                                        >> wrote:
                                        >> >> >>
                                        >> >> >> I'm surprised it works as you've laid it out (which is not to say
                                        >> >> that
                                        >> >> >> it's wrong, since I presume you've tested it).
                                        >> >> >>
                                        >> >> >> The reason I put "1000 + GP", is so that I'm guaranteed to have a
                                        >> >> >> 4-digit
                                        >> >> >> number. In your case then, it would be clearer to turn this:
                                        >> >> >> CONCAT(1000 + CAST(f2.GP as CHAR(3))
                                        >> >> >>
                                        >> >> >> Into this:
                                        >> >> >> CONCAT(CAST(1000 + f2.GP as CHAR(4))
                                        >> >> >>
                                        >> >> >> In your version, what would a number added to a variable that is
                                        >> >> numeric
                                        >> >> >> but been cast as a string return?
                                        >> >> >>
                                        >> >> >> Furthermore, if you can cast something to force three characters
                                        >> (I
                                        >> >> >> presume casting 1 GP in CHAR3 would return 001 ?), then there's no
                                        >> >> point
                                        >> >> >> to even adding in the 1000 to begin with.
                                        >> >> >>
                                        >> >> >> I don't do much MySQL, so anything more that you can show us would
                                        >> be
                                        >> >> >> welcomed.
                                        >> >> >>
                                        >> >> >> Thanks, Tom
                                        >> >> >>
                                        >> >> >>
                                        >> >> >>
                                        >> >> >>
                                        >> >> >> > I just figured it out:
                                        >> >> >> >
                                        >> >> >> > SELECT
                                        >> >> >> > f2.yearid,
                                        >> >> >> > f2.playerid,
                                        >> >> >> > MID(MAX(CONCAT(1000 + CAST(f2.GP as CHAR(3)),"_",f2.pos)),6) AS
                                        >> >> >> PrimPos,
                                        >> >> >> > Max(f2.GP) AS GP_Pos, SUM(f2.GP) AS GP_sum,
                                        >> >> >> > IF(SUM(1) = 1,"",MID(MIN(CONCAT(1000 + CAST(f2.GP as
                                        >> >> >> > CHAR(3)),"_",f2.pos)),6)) AS LastPos
                                        >> >> >> > FROM
                                        >> >> >> > (SELECT
                                        >> >> >> > yearid,
                                        >> >> >> > playerid,
                                        >> >> >> > pos,
                                        >> >> >> > SUM(g) as GP
                                        >> >> >> > from fielding
                                        >> >> >> > group by yearid, playerid, pos) AS f2
                                        >> >> >> > GROUP BY f2.yearid, f2.playerid
                                        >> >> >> >
                                        >> >> >> > --- In baseball-databank@yahoogroups.com, "Tangotiger" <tom@>
                                        >> >> wrote:
                                        >> >> >> >>
                                        >> >> >> >> Can you show us what functions you've tried?
                                        >> >> >> >>
                                        >> >> >> >> Here's a related one in Oracle I've used (for something else):
                                        >> >> >> >> substr(min(to_char(order_id + 90000,'00000') ||
                                        >> >> >> >> to_char(mlbam_id,'000000')),8,6) as best_mlbam_id
                                        >> >> >> >>
                                        >> >> >> >> See if the idea there helps any.
                                        >> >> >> >>
                                        >> >> >> >> Tom
                                        >> >> >> >>
                                        >> >> >> >>
                                        >> >> >> >>
                                        >> >> >> >> > 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
                                        >> >> >> >> >>
                                        >> >> >> >> >
                                        >> >> >> >> >
                                        >> >> >> >> >
                                        >> >> >> >>
                                        >> >> >> >>
                                        >> >> >> >> ---------------------------------------------
                                        >> >> >> >> The Book--Playing The Percentages In Baseball
                                        >> >> >> >> http://www.InsideTheBook.com
                                        >> >> >> >>
                                        >> >> >> >
                                        >> >> >> >
                                        >> >> >> >
                                        >> >> >>
                                        >> >> >>
                                        >> >> >> ---------------------------------------------
                                        >> >> >> The Book--Playing The Percentages In Baseball
                                        >> >> >> http://www.InsideTheBook.com
                                        >> >> >>
                                        >> >> >
                                        >> >> >
                                        >> >> >
                                        >> >>
                                        >> >>
                                        >> >> ---------------------------------------------
                                        >> >> The Book--Playing The Percentages In Baseball
                                        >> >> http://www.InsideTheBook.com
                                        >> >>
                                        >> >
                                        >> >
                                        >> >
                                        >>
                                        >>
                                        >> ---------------------------------------------
                                        >> The Book--Playing The Percentages In Baseball
                                        >> http://www.InsideTheBook.com
                                        >>
                                        >
                                        >
                                        >


                                        ---------------------------------------------
                                        The Book--Playing The Percentages In Baseball
                                        http://www.InsideTheBook.com
                                      • Bryan Walko
                                        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,
                                        Message 19 of 20 , May 14, 2009
                                        • 0 Attachment
                                          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


                                        • William
                                          You are right, it looks like I don t need to convert it to string.
                                          Message 20 of 20 , 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
                                            > >
                                            > >
                                            > >
                                            >
                                          Your message has been successfully submitted and would be delivered to recipients shortly.