Retrieve last row in mysql with shared primary key

Question

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)
);

score 0 · Accepted Answer · answered Feb 02 '18 at 09:14

0

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

answered Feb 02 '18 at 09:14

John Woo

258,903
69
498
492

How is this inner join query different from normal where with in clause on sub-query ? Any performance issues? select * from table a where a.id, a.ts in (select id, max(ts) from table b group by id) – uhs Feb 02 '18 at 11:06

score 0 · Answer 2 · answered Feb 02 '18 at 10:33

0

You can either pass the item_id into a variable or set it directly into the query and as long as you have an index for the table containing both fields, the query optimizer will utilise it and give you the latest record for the item_id:

SET @ID := 10;
SELECT item_id, ts 
FROM table 
WHERE item_id = @ID
ORDER BY item_id,ts DESC
LIMIT 1

answered Feb 02 '18 at 10:33

amfipolis

19
3

Using Min, Max functions explicitly demand a full table or full index scan which is not optimal. It is used only for portability between SQL variants. – amfipolis Feb 02 '18 at 10:35

Retrieve last row in mysql with shared primary key

2 Answers2