MAX and GROUP BY don't return expected values

After searching for a couple of hours on the website, I couldn't find the right answer to my problem, or the answer was too complicated. I have some statistics, from a minecraft server and I want to know which blocks are created the most by whom. So I started with GROUP BY and MAX, but it didn't return the right values. The MAX value is good, but it conflicts with the itemid and the playerid. It just selects the first id and playername. (NOTE: each playerid has a seperate row for each item!!!) The query is as following:

SELECT  `playername` ,  `itemid` , MAX( destroyed ) 
FROM  `blockstats_typed` 
GROUP BY  `itemid` 
LIMIT 0 , 30

The fields that are there are playername, itemid, created and destroyed. I hope you guys can help me...

If you need more info, just say so!

Edit 1 (Table schema):

Column	type	description
id	int(10)	id of the row (AI)
playername	varchar(50)	playername
itemid	smallint(5)	id of the item
created	int(10)	times created
destroyed	int(10)	times destroyed

Solution

Only MySQL and PostgreSQL (but PostgreSQL does it in a much more limited way; and a comment tells me that SQLite mimics MySQL in this) allow you to omit columns (expressions) from the GROUP BY clause that are in the select-list but are not aggregates. If you want the item ID, the maximum number of times it was destroyed, and the user ID (or IDs) that did that, you have to write a more complex query.

So, you might be after:

SELECT playername,  itemid, MAX(destroyed) 
  FROM blockstats_typed
 GROUP BY playername, itemid 
 LIMIT 0, 30

Or you might be after:

SELECT b.PlayerName, s.ItemID, s.MaxDestroyed
  FROM BlockStats_Typed AS b
  JOIN (SELECT ItemID, MAX(Destroyed) AS MaxDestroyed
          FROM BlockStats_Typed
         GROUP BY ItemID
         LIMIT 0, 30
       ) AS s
    ON b.ItemID = s.ItemID AND b.Destroyed = s.MaxDestroyed;

Note that if some item is destroyed, say, 237,000 times by two players, you will get back both players' names (if that ItemID is in the top 30, of course).

Both are valid queries; they do different things, though. You'll have to decide which, if either, is correct for what you're trying to do. And if neither is correct, you probably need to clarify your question.

(I'm not certain that the LIMIT is allowed where I've written it. If not, place it at the end. And you might want to add an ORDER BY clause with a DESC qualifier to ensure that the limit clause shows the most interesting rows; GROUP BY does not guarantee sorting.)