【Question】

I have a database of baseball plays with a PlayerID and a TypeID (Types of play: double, strike out, etc). The data looks something like this:

+----------+--------+

| playerid | typeid |

+----------+--------+

| 2 | 4 |

| 2 | 4 |

| 2 | 7 |

| 3 | 7 |

| 3 | 7 |

| 3 | 7 |

| 3 | 26 |

| 3 | 7 |

I'm trying to find which players had the most of each type of play. E.g. Jim (PlayerID 3) had the most strike outs (TypeID 7) and Bob (PlayerID 2) had the most home runs (TypeID 4), which should result in the following table:

+----------+--------+----------------+

| playerid | typeid | max(playcount) |

+----------+--------+----------------+

| 2 | 4 | 12 |

| 3 | 7 | 9 |

| 3 | 26 | 1 |

My best attempt so far is to run:

SELECT playerid,typeid,MAX(playcount) FROM

(

SELECT playerid,typeid,COUNT(*) playcount FROM plays GROUP BY playerid,typeid

) AS t GROUP BY typeid;

Which returns the proper maximums of each type, but the associated PlayerIDs are all wrong and I can't figure out why. I'm sure I'm missing something simple (or making this overly complicated) but can't figure it out. Any ideas?

 

A correct solution:

SELECT a.*

 FROM (

SELECT playerid

,typeid

,COUNT(*) playcount

FROM plays

GROUP BY playerid,typeid

) a

 LEFT JOIN

(

SELECT playerid

,typeid

,COUNT(*) playcount

FROM @lays

GROUP BY playerid,typeid

) b

ON a.typeid = b.typeid

AND a.playcount < b.playcount

WHERE b.playerid IS NULL

 

【Answer】

We can’t get the record containing the max value in a certain field directly. We need to make a comparison using a subquery. But it will become easy if we can retrieve data out from the database to process. Here we handle it in SPL (Structured Process Language):

	A
1	$SELECT  playerid,typeid,COUNT(*) playcount FROM   plays GROUP BY playerid,typeid
2	=A1.group(typeid).(~.maxp(playcount))

A1: Retrieve data out, group it by playerid and typeid, and count plays in each group with a SQL statement.

A2: Group A1’s table by typeid to get the player with the most play count in each type of play, which is the record in each group where playcount is the greatest.

The SPL script can be integrated into a third application. See How to Call an SPL Script in Java to learn more.