It was my long standing problem when querying the latest history of a record. The scenario is that I have a parent table and its transaction log (say, history records) are stored in a child table linked by the parent’s primary key id. The parent table stores the main information regarding a transaction while the child table stores historical data of what happened in the parent’s table info.
Well, the first thing that I can think of was to create a sub-query of my child table, MAX it’s identity and GROUP BY the parent’s id.
SELECT p.*
, c.details
, c.date_created
FROM (
SELECT tmp.*
, c.details
FROM (
SELECT parent_id
, MAX(date_created) date_created
FROM children
GROUP BY parent_id
) tmp
INNER join children c
ON tmp.parent_id = c.parent_id
and tmp.date_created = c.date_created
) c
INNER join parent p
ON c.parent_id = p.id
, c.details
, c.date_created
FROM (
SELECT tmp.*
, c.details
FROM (
SELECT parent_id
, MAX(date_created) date_created
FROM children
GROUP BY parent_id
) tmp
INNER join children c
ON tmp.parent_id = c.parent_id
and tmp.date_created = c.date_created
) c
INNER join parent p
ON c.parent_id = p.id
Though I am getting my desired result set, the code below is more efficient.
SELECT p.*
, c.details
, c.date_created
FROM parent p
INNER join children c
ON p.id = c.parent_id
WHERE c.id =
(
SELECT MAX(c2.id)
FROM children c2
WHERE c2.parent_id = p.id
GROUP BY parent_id
)
, c.details
, c.date_created
FROM parent p
INNER join children c
ON p.id = c.parent_id
WHERE c.id =
(
SELECT MAX(c2.id)
FROM children c2
WHERE c2.parent_id = p.id
GROUP BY parent_id
)
Why didn’t I think of this one first? Classic example of Columbus’ Egg. Again, there is always a simple solution behind a complex problem.
0 Responses to “Querying the Latest Record”
Leave a Reply