Querying the Latest Record

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

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
            )

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.