CODEWITHSUNDEEP
mysqlsqlgroup-by
user9219182
user9219182

Reputation: 349

Multiple GROUP BY to count MIN elements?

Suppose in MySQL I have a table comments, which has fields post_id, author_id, date, and comment.

If I want the first comment for each post, I can do

SELECT post_id, author_id, comment, MIN(date) AS comment_date 
FROM comments 
GROUP BY post_id;

If I want to get the authors who have made the most comments, I can do

SELECT author_id, COUNT(*) AS total_comments 
FROM comments 
GROUP BY author_id 
ORDER BY total_comments DESC;

I want to combine these, to answer the question "What authors have made the most first comments?" How do I do this?

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is broken SQL:

SELECT post_id, author_id, comment, MIN(date) AS comment_date 
FROM comments 
GROUP BY post_id;

The SELECT columns and GROUP BY are inconsistent. Just because your database happens to allow the syntax doesn't mean that it does what you want.

To answer this question:

"What authors have made the most first comments?"

The correct form of your first query is:

select c.*
from comments c
where c.date = (select min(c2.date)
                from comments c2
                where c2.post_id = c.post_id
               );

There are other ways to write this logic as well, notably using window functions.

Then you just want aggregation:

select author_id, count(*)
from comments c
where c.date = (select min(c2.date)
                from comments c2
                where c2.post_id = c.post_id
               )
group by author_id
order by count(*) desc;

Note: This assumes that the minimum date does indeed correspond to "the first comment", even when multiple authors make comments on the same date.

Upvotes: 2

Related Questions