CODEWITHSUNDEEP
sqlmysql
yegor256
yegor256

Reputation: 105210

How can I avoid a sub-query?

This is my table:

ID   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?

Upvotes: 4

Views: 11510

Answers (3)

symcbean
symcbean

Reputation: 48387

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

Upvotes: 0

Lee
Lee

Reputation: 13542

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;

Upvotes: 4

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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.

Upvotes: 6

Related Questions