Join first valid row from another table in innodb

I have 2 tables, one that holds records with some data related to it and another that holds historic data for first table.

开发者_运维技巧

The historic table may have many rows for each row of the main table, what i want to do is join on the newest record from the historic table when pulling out one or more from the main table.

I've tried joining a subquery using a group by id, that didn't work, I cant use a limit because I want to select more than one record.

---

Assuming that historical table has PK composed of original id and timestamp

SELECT table.*, ht1.*
FROM table INNER JOIN historical_table ht1
     ON table.id = ht1.id
     LEFT JOIN historical table ht2
     ON ht1.id = ht2.id AND ht1.timestamp < ht2.timestamp
WHERE ht2.timestamp IS NULL

Main part of the logic is select rows for which there are no newer records (ht2.timestamp is null)

This is a common max-per-group so question (and there are other ways to go about it)

---

One way to go about it:

select record.id
(select history.id from history where history.record_id = record.id order by history.id desc limit 1) as history_id
from record

If you want full queries on that you can just use the id's from that query as a sub query. Lots of other ways to go about this too :-)