Trouble with sql query using group by and order by

I'm trying to select all comments that are the last in their conversation. Comments are in the same conversation if they have the same object_id and type_id. This is th开发者_如何学Ce query I have right now:

select * from comments 
 where user_id != #{current_user.id} 
 group by type_id, object_id 
 order by created_at desc

It works except the comments aren't always the last in the conversation. How can I do that? I need it to work in mysql and sqlite3.

If you have troubles with a GROUp By the 1st thing to do is avoiding using '*'. This is an extension of the standard GROUP BY of SQL, provided by MySQL.

Classic group by must include all non-aggregate columns used in the select. mySQl let you avoiding the very basic fact and get some randomness on the result:

A similar MySQL extension applies to the HAVING clause. The SQL standard does not permit the HAVING clause to name any column not found in the GROUP BY clause if it is not enclosed in an aggregate function. MySQL permits the use of such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate.

So try an updated version of the query with a plain select.

This query isn't very pretty (probably very slow to run on your server), but...

SELECT 
    MAX(created_at) created_at,
    * 
FROM 
    comments 
WHERE 
    user_id != #{current_user.id} 
GROUP BY 
    type_id, 
    object_id 
ORDER BY
    created_at DESC

EDIT: Just noticed this doesn't work. What's needed is the table schema to solve this.

Here's a guess, but need more information on your schema to be certain. Do you need/want only the very last comment, or are you trying to find the last n comments per "conversation"?

  SELECT *
    FROM comments
   WHERE user_id != ...
ORDER BY type_id,
         object_id,
         created_at DESC

You cannot get the results that you want using only a GROUP BY query. The reason is that there's no way to instruct the RDBMS to select the comment text corresponding to the last comment in the GROUPed set.

Instead do this:

 SELECT * FROM comments 
  WHERE user_id != #{current_user.id} AND created_at = 
    (SELECT MAX(created_at) FROM comments WHERE user_id != #{current_user.id} )

or

 SELECT * FROM comments c1
  WHERE user_id != #{current_user.id} AND NOT EXISTS
    (SELECT * FROM comments c2 
      WHERE user_id != #{current_user.id} AND c2.created_at > c1.created_at )

(I used comment_id since it's not clear what identifies comments, but if it's object_id then use that instead).

If you want to select the last comments within each group of type_id and object_id, use a subquery:

select c1.*
from comments c1
join (
  select type_id, object_id, max(created_at) last_comment_created_at
  from comments
  where user_id != #{current_user.id} /* indicates 'comment'? */
  group by type_id, object_id
) c2
on c2.type_id = c1.type_id
  and c2.object_id = c1.object_id
  and c2.last_comment_created_at = c1.created_at

This works except there are multiple comments having exactly same type_id, object_id and created_at' value. So if there is a column like 'comment_order', that is preferable to 'created_at'.