CODEWITHSUNDEEP
mysqlsql
Alexander Yashchyshen
Alexander Yashchyshen

Reputation: 3

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:
enter image description here

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

Upvotes: 0

Views: 868

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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.

Upvotes: 0

trincot
trincot

Reputation: 351328

If you are on MySql 8, you can use row_number:

SELECT com.text, com.can_id 
FROM (SELECT comments.*,
             row_number() over (partition by can_id order by id desc) rn
      FROM   comments) as com 
WHERE rn = 1;

If you are on MySql 5.6+, you can (ab)use group_concat:

SELECT SUBSTRING_INDEX(group_concat(text order by id desc), ',', 1), 
       can_id 
FROM   comments 
GROUP BY can_id;

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82524

The order by clause in the inner select is redundant since it's being used as a table, and tables in a relational database are unordered by nature.
While other databases such as SQL Server will treat is as an error, I guess MySql simply ignores it.

I think you are looking for something like this:

SELECT text, can_id
FROM comments
ORDER BY id DESC
LIMIT 1

This way you get the text and can_id associated with the highest id value.

Upvotes: 0

Related Questions