Q:

The last value using GROUP BY

I need to take the last value from table where can_id equal.
So I've tried this SQL query
SELECT com.text, com.can_id 
FROM (SELECT * FROM comments ORDER BY id DESC) as com 
GROUP BY com.can_id

But if I change ASC / DESC in the first select, the second select will just group without sorting and take the value with the first id
This select will be used like left join in the query.
Example:

I need to get com.text with value "text2" (lasts)

A:

In any version of MySQL, the following will work:
SELECT c.*
FROM comments c
WHERE c.id = (SELECT MAX(c2.id)
              FROM comments c2
              WHERE c2.can_id = c.can_id
             );

With an index on comments(can_id, id), this should also have the best performance.
This is better than a group by approach because it can make use of an index and is not limited by some internal limitation on intermediate string lengths.
This should have better performance than row_number() because it does not assign a row number to each row, only then to filter things out.

