How to avoid a side request?

Question

This is my table:

ID   KEY    VALUE
1    alpha  100
2    alpha  500
3    alpha  22
4    beta   60
5    beta   10

I am trying to get a list of all KEY-s with their latest values ​​(where IDis at maximum):

ID   KEY     VALUE
3    alpha   22
5    beta    10

In MySQL, I use this query, which is not efficient:

SELECT temp.* FROM
  (SELECT * FROM t ORDER BY id DESC) AS temp
GROUP BY key

Can a subquery be avoided in this case?

Answer 1

Use INNER JOIN to join your maximum ids.

SELECT  t.*
FROM    t
        INNER JOIN (
          SELECT  ID = MAX(ID)
          FROM    t
          GROUP BY
                  key
        ) tm ON tm.ID = t.ID                  

Assuming the index column is indexed, this is probably as fast as retrieving it.

Answer 2

here is the mysql documentation page discussing this section .

he presents three different options.

, :

SELECT t1.id, t1.k, t1.value
FROM t t1
LEFT JOIN t t2 ON t1.k = t2.k AND t1.id < t2.id
WHERE t2.k IS NULL;

Answer 3

, ,