【Question】

I have a database of baseball plays with a PlayerID and a TypeID (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 have the most of each type of play. For example, Jim (PlayerID 3) has the most strike outs (TypeID 7) and Bob (PlayerID 2) has 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;

 

The query 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.

 

Someone provides 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】
SQL can’t get a record with the maximum value directly. It needs a subquery to make a comparison first, which make the code complicated. You can do it in SPL (Structured Process Language) with a two-liner:

	A
1	$select   playerid,typeid,count (*) playcount from plays group by playerid,typeid
2	=A1.group(typeid).(~.maxp(playcount))

An SPL script can be embedded into another application. See How to Call an SPL Script in Java.