MySQL SELECT most frequent by group

by 
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.

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Retrieving the last record in each group – MySQL
  3. In SQL, how to select the top 2 rows for each group
  4. Using ORDER BY and GROUP BY together
  5. Sum results of a few queries and then find top 5 in SQL
  6. SQL select only rows with max value on a column [duplicate]
  7. How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?
  8. Error related to only_full_group_by when executing a query in MySql
  9. Get records with max value for each group of grouped SQL results
  10. SELECT list is not in GROUP BY clause and contains nonaggregated column …. incompatible with sql_mode=only_full_group_by
  11. MySQL Query GROUP BY day / month / year
  12. MySQL order by before group by
  13. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  14. Grouping into interval of 5 minutes within a time range
  15. Get records with highest/smallest per group
  16. MySQL – Control which row is returned by a group by
  17. MySQL Order before Group by
  18. mysql select top n max values
  19. MySQL Group By and Sum total value of other column
  20. What does SQL clause “GROUP BY 1” mean?
  21. ORDER BY date and time BEFORE GROUP BY name in mysql
  22. Mysql select distinct
  23. GROUP BY – do not group NULL
  24. Select all months within given date span, including the ones with 0 values
  25. How to select data where a field has a min value in MySQL?
  26. sql group by versus distinct
  27. MySQL Group By Hours
  28. Select only last value using group by at mysql
  29. group by range in mysql
  30. How do I limit the number of rows per field value in SQL?

Leave a Comment