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

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

Expand Messages
  • Tangotiger
    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
    • Show all 20 messages in this topic