MySQL SELECT most frequent by group

SELECT t1.*
FROM (SELECT tag, category, COUNT(*) AS count
      FROM tags INNER JOIN stuff USING (id)
      GROUP BY tag, category) t1
LEFT OUTER JOIN 
     (SELECT tag, category, COUNT(*) AS count
      FROM tags INNER JOIN stuff USING (id)
      GROUP BY tag, category) t2
  ON (t1.tag = t2.tag AND (t1.count < t2.count 
      OR t1.count = t2.count AND t1.category < t2.category))
WHERE t2.tag IS NULL
ORDER BY t1.count DESC;

---

I agree this is kind of too much for a single SQL query. Any use of GROUP BY inside a subquery makes me wince. You can make it look simpler by using views:

CREATE VIEW count_per_category AS
    SELECT tag, category, COUNT(*) AS count
    FROM tags INNER JOIN stuff USING (id)
    GROUP BY tag, category;

SELECT t1.*
FROM count_per_category t1
LEFT OUTER JOIN count_per_category t2
  ON (t1.tag = t2.tag AND (t1.count < t2.count 
      OR t1.count = t2.count AND t1.category < t2.category))
WHERE t2.tag IS NULL
ORDER BY t1.count DESC;

But it's basically doing the same work behind the scenes.

You comment that you could do a similar operation easily in application code. So why don't you do that? Do the simpler query to get the counts per category:

SELECT tag, category, COUNT(*) AS count
FROM tags INNER JOIN stuff USING (id)
GROUP BY tag, category;

And sort through the result in application code.

SELECT  tag, category
FROM    (
        SELECT  @tag <> tag AS _new,
                @tag := tag AS tag,
                category, COUNT(*) AS cnt
        FROM    (
                SELECT  @tag := ''
                ) vars,
                stuff
        GROUP BY
                tag, category
        ORDER BY
                tag, cnt DESC
        ) q
WHERE   _new

On your data, this returns the following:

'automotive',  8
'ba',          8
'bamboo',      8
'bananatree',  8
'bath',        9

Here's the test script:

CREATE TABLE stuff (tag VARCHAR(20) NOT NULL, category INT NOT NULL);

INSERT
INTO    stuff
VALUES
('automotive',8),
('ba',8),
('bamboo',8),
('bamboo',8),
('bamboo',8),
('bamboo',8),
('bamboo',8),
('bamboo',10),
('bamboo',8),
('bamboo',9),
('bamboo',8),
('bamboo',10),
('bamboo',8),
('bamboo',9),
('bamboo',8),
('bananatree',8),
('bananatree',8),
('bananatree',8),
('bananatree',8),
('bath',9);

(Edit: forgot DESC in ORDER BYs)

Easy to do with a LIMIT in the subquery. Does MySQL still have the no-LIMIT-in-subqueries restriction? Below example is using PostgreSQL.

=> select tag, (select category from stuff z where z.tag = s.tag group by tag, category order by count(*) DESC limit 1) AS category, (select count(*) from stuff z where z.tag = s.tag group by tag, category order by count(*) DESC limit 1) AS num_items from stuff s group by tag;
    tag     | category | num_items 
------------+----------+-----------
 ba         |        8 |         1
 automotive |        8 |         1
 bananatree |        8 |         4
 bath       |        9 |         1
 bamboo     |        8 |         9
(5 rows)

Third column is only necessary if you need the count.