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

Re: HACKS - Positions

Expand Messages
  • 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 1 of 20 , May 11, 2009
    View Source
    • 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 2 of 20 , May 13, 2009
      View Source
      • 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 3 of 20 , May 13, 2009
        View Source
        • 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 4 of 20 , May 14, 2009
          View Source
          • 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 5 of 20 , May 14, 2009
            View Source
            • 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 6 of 20 , May 14, 2009
              View Source
              • 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.