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

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

Expand Messages
  • 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 1 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
      >


    • 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 2 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 3 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 4 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 5 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 6 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 7 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 8 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 9 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 10 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 11 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.