[SOLVED] Retrieve last row in mysql with shared primary key

Retrieve last row in mysql with shared primary key

I have a table with a primary key of 2 values - id and timestamp. The table contains multiple rows for each item_id, and the timestamp is what should make it unique. Can I execute an efficient query considering this setup? I want to find the latest appearance of an item_id. Any ideas?

CREATE TABLE IF NOT EXISTS table (
  item_id varchar(30) NOT NULL,
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ...
  PRIMARY KEY  (item_id, ts)
);

Solution

Do you mean MAX()?

SELECT item_id, MAX(ts) as TS
FROM `table`
GROUP BY item_id

However, if you wanted to get all columns based from the latest item_id, you can put this query inside subquery and join it to the table itself:

SELECT  a.*
FROM    `table` a
        INNER JOIN
        (
            SELECT item_id, MAX(ts) as TS
            FROM `table`
            GROUP BY item_id
        ) b ON a.item_id = b.item_id
                AND a.ts = b.TS