Me.Thoughts.ToString()

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.

01   select      p.*
02           ,   c.details
03           ,   c.date_created
04   from        (
05               select      tmp.*
06                       ,   c.details
07               from        (
08                           select      parent_id
09                                   ,   max(date_created) date_created
10                           from        children
11                           group by    parent_id
12                           ) tmp
13               inner join  children c
14               on          tmp.parent_id = c.parent_id
15               and         tmp.date_created = c.date_created
16               ) c
17   inner join  parent p
18   on          c.parent_id = p.id

Though I am getting my desired result set, the code below is more efficient.

01   select      p.*
02           ,   c.details
03           ,   c.date_created
04   from        parent p
05   inner join  children c
06   on          p.id = c.parent_id
07   where       c.id =
08               (
09               select      max(c2.id)
10               from        children c2
11               where       c2.parent_id = p.id
12               group by    parent_id
13               )

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.