- May 13, 2009No, 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

> - << Previous post in topic Next post in topic >>