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

3762Re: HACKS - Positions

Expand Messages
  • William
    May 11, 2009
      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
      >
    • Show all 20 messages in this topic