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

3766Re: HACKS - Positions

Expand Messages
  • William
    May 13 3:35 PM
    • 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
      >
    • Show all 20 messages in this topic