How can I avoid a sub-query?

This is my table:

ID 开发者_StackOverflow中文版  KEY    VALUE
1    alpha  100
2    alpha  500
3    alpha  22
4    beta   60
5    beta   10

I'm trying to retrieve a list of all KEY-s with their latest values (where ID is in its maximum):

ID   KEY     VALUE
3    alpha   22
5    beta    10

In MySQL I'm using this query, which is not effective:

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

Is it possible to avoid a sub-query in this case?

Use an INNER JOIN to join with your max ID's.

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

Assuming the ID column is indexed, this is likely as fast as its going to get.

here is the mysql documentation page that discusses this topic.

it presents three distinct options.

the only one that doesn't involve a sub query is:

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;

There's page in the manual explaining how to do this