Group by MySQL mess

Question

I am really having a trouble figuring this one out.

I have a table 'Comments':

cmt_id (primary key, auto incr), thread_id, cmt_text

And I have these records in it:

cmt_id   thread_id       cmt_txt
5002     1251035762511   Alright, I second this. 
5003     1251036148894   Yet another comment.
5001     1251035762511   I am starting a thread on this.

I want to now, get the minimum cmt_id record in EACH thread. So, I did an aggregation query this way:

SELECT cmt_id, thread_id, cmt_text, MIN(cmt_id) FROM comments 
GROUP BY thread_id;

However, I end up with this:

cmt_id   thread_id    cmt_text                    MIN(cmt_id)
5002    1251035762511  Alright, I second this.  5001
5003    1251036148894  Yet another comment.      5003

For the thread with thread_id "1251035762511", I am always getting the comment with cmt_id 5002 as the record with minimum comment id.. I even tried inserting new records, cmt_id 5002 always comes as MIN and not the record with cmt_id 5001.

Answer 1

This may be just a typo, but the SQL query you have entered actually has MIN(cmt_id) not MAX(cmt_id) in it.

SELECT cmt_id, thread_id, cmt_text, MIN(cmt_id) FROM comments 
GROUP BY thread_id;

I think you may need to put a rubber duck by your monitor and talk to it

If you just want the max comment id then

SELECT MAX(cmt_id), thread_id FROM comments GROUP BY thread_id

will suffice. If you need the additional field you'll need to use a subquery as per other answers here.

Answer 2

SELECT *
FROM comments
     INNER JOIN (
       SELECT cmt_id = MIN(cmt_id), thread_id
       FROM comments
       GROUP BY thread_id
     ) cmin ON cmin.cmt_id = comments.cmt_id

Answer 3

You have to have something in the lines of:

SELECT 
   cmt_id, 
   thread_id, 
   cmt_text 
FROM comments 
WHERE cmt_id IN (
         SELECT 
            MIN(cmt_id) 
         FROM comments 
         GROUP BY thread_id);

This is how SQL works, in your query it merges the rows leaving not the row with min cmt_id but random one (or the last one but I wouldn't count on it). In the above query it would first find all the min ids and then get extended information for each one of them.

Answer 4

I don't know why MySQL allows you to do that, but normally in SQL the following query is not valid

SELECT cmt_id, thread_id, cmt_text, MIN(cmt_id) FROM comments 
GROUP BY thread_id;

You shouldn't be able to specify a SELECT column if it is not

1. A GROUP BY column
2. An aggregate function (e.g. MIN, MAX, COUNT, SUM...)

In your case I would use this select

SELECT cmt_id, thread_id, cmt_text FROM comments INNER JOIN (
  SELECT MAX(cmt_id) AS cmt_id FROM comments 
  GROUP BY thread_id
) a ON a.cmt_id = comments.cmt_id