Selecting the most recent elements from a log table 2

[grande]

Okay, here's what I have:

Table: element_logs
Columns:
id (int, PK)
parent_id (int)
element_id (int, FK)
updated_at (datetime)

What I'd like to do is find the most recent element_ids with the same parent_id.

That is, given a parent_id of say 5, and a date of yesterday, I want the id of each most recent element_id.

I have a feeling it's easier than I'm making it. Any suggestions/help?

-------------------------
Matt Grande
C# Master.
Ruby on Rails Admirer.
ActionScript Student.
JavaScript Hate-Monger.

 

[RiverGuy]

SELECT a.*
FROM YourTable a
INNER JOIN
  (SELECT parent_id, MAX(updated_at) AS MaxUpdated
   FROM YourTable GROUP BY parent_id) b
ON a.parent_id = b.parent_id AND a.updated_at = b.MaxUpdated

 

[gmmastros]

Does this return what you want?

Select element_logs.*
From   element_logs
       Inner Join (
         Select element_id, Max(updated_At) As LastUpdated
         From   element_logs
         Where  parent_id = 5
         Group By element_id
         ) as A
         On  element_logs.element_id = A.element_id
         and element_logs.updated_at = A.LastUpdated

If this works, and you would like me to explain, let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[grande]

Thanks to both RiverGuy and gmmastros! Nicely done. I greatly appreciate it.

-------------------------
Matt Grande
C# Master.
Ruby on Rails Admirer.
ActionScript Student.
JavaScript Hate-Monger.