CODEWITHSUNDEEP
mysqlsqljoininnodbgreatest-n-per-group
Sam
Sam

Reputation: 5536

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.

Upvotes: 0

Views: 568

Answers (2)

Dytut
Dytut

Reputation: 317

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

Upvotes: 1

Unreason
Unreason

Reputation: 12704

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)

Upvotes: 2

Related Questions