Browse Groups

• 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
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
> >> >> >
> >> >> > --- 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:
> >> >> >>
> >> >> >>
> >> >> >> 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
>
• 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 1 of 20 , May 13, 2009
View Source
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
>> >> >> >
>> >> >> > --- 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:
>> >> >> >>
>> >> >> >>
>> >> >> >> 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
• 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 1 of 20 , May 13, 2009
View Source
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
> >> >> >> >
> >> >> >> > --- 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
>
• 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 1 of 20 , May 14, 2009
View Source
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
>> >> >> >> >
>> >> >> >> > --- 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
• 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 1 of 20 , May 14, 2009
View Source
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
>>
>> --- 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:
>> >
>> >
>> > 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

• You are right, it looks like I don t need to convert it to string.
Message 1 of 20 , May 14, 2009
View Source
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
> > >>
> > >> --- 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:
> > >> >
> > >> >
> > >> >
> > >> > 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.
• Changes have not been saved
Press OK to abandon changes or Cancel to continue editing
• Your browser is not supported
Kindly note that Groups does not support 7.0 or earlier versions of Internet Explorer. We recommend upgrading to the latest Internet Explorer, Google Chrome, or Firefox. If you are using IE 9 or later, make sure you turn off Compatibility View.