Using MAX within INNER JOIN - SQL

Question

I have two tables, one called facebook_posts and the other called facebook_post_metrics.

facebook_posts looks like

NAME    id                      
a         1           
b         1            
c         4             
d         4             

facebook_post_metrics looks like

number    FBID       Date_Executed             User_Executed
1         1        2012-09-18 16:10:44.917   admin
2         1        2012-09-25 11:39:01.000   jeff
3         4        2012-09-25 13:20:09.930   steve
4         4        2012-09-25 13:05:09.953   marsha

So the common column that would be used for the inner join is id from the facebook_posts table and FBID from the facebook_post_metrics.

So after the inner Join, the table should look like:

  name  number           FBID            Date_Executed             User_Executed
   a       1               1             2012-09-18 16:10:44.917   admin
   b       2               1             2012-09-25 11:39:01.000   jeff
   c       3               4             2012-09-25 13:20:09.930   steve
   d       4               4             2012-09-25 13:05:09.953   marsha

However, I want to include another condition while doing this inner join. Basically, I just want to have the most updated entry for the joined table above. I know I would use max(date_executed) and then group it by FBID. But I'm not sure which part of the SQL Query that would go into when using INNER JOIN. Please help me out.

Bottom line...I'd like to end up with a table looking like this:

  name  number           FBID            Date_Executed             User_Executed
   b       2               1             2012-09-25 11:39:01.000   jeff
   c       3               4             2012-09-25 13:20:09.930   steve

Answer 1

With a problem like this, I recommend breaking it down into pieces and putting it back together.

Finding the date of the most recent facebook_posts_metrics row is easy, like this:

SELECT fbid, MAX(date_executed) AS latestDate
FROM facebook_post_metrics
GROUP BY fbid;

So, to get the entire row, you want to join the original table with those results:

SELECT fpm.*
FROM facebook_post_metrics fpm
JOIN(
  SELECT fbid, MAX(date_executed) AS latestDate
  FROM facebook_post_metrics
  GROUP BY fbid) t ON t.fbid = fpm.fbid AND t.latestDate = fpm.date_executed;

Last, all you have to do is join that with facebook_posts table to get the name:

SELECT fp.name, fpm.number, fpm.fbid, fpm.date_executed, fpm.user_executed
FROM facebook_posts fp
JOIN(
  SELECT fpm.*
  FROM facebook_post_metrics fpm
  JOIN(
    SELECT fbid, MAX(date_executed) AS latestDate
    FROM facebook_post_metrics
    GROUP BY fbid) t ON t.fbid = fpm.fbid AND t.latestDate = fpm.date_executed
  ) fpm ON fpm.fbid = fp.id AND fpm.date_executed = fp.updated_at;

Here is an SQL Fiddle example.

EDIT

Based on your comments and looking over your design, I believe you can do something like this. First, get the latest facebook_post_metrics which I have described above. Then, get the latest facebook_post by using a similar method. This searches the most recent updated_at value of the facebook post. If you want to use a different date column, just change that:

SELECT fp.*
FROM facebook_posts fp
JOIN(
  SELECT id, MAX(updated_at) AS latestUpdate
  FROM facebook_posts
  GROUP BY id) t ON t.id = fp.id AND t.latestUpdate = fp.updated_at;

Last, you can join that query with the one for facebook_post_metrics on the condition that the id and fbid columns match:

SELECT fp.name, fpm.number, fpm.fbid, fpm.date_executed, fpm.user_executed
FROM(
  SELECT fp.*
  FROM facebook_posts fp
  JOIN(
    SELECT id, MAX(updated_at) AS latestUpdate
    FROM facebook_posts
    GROUP BY id) t ON t.id = fp.id AND t.latestUpdate = fp.updated_at) fp
JOIN(
  SELECT fpm.*
  FROM facebook_post_metrics fpm
  JOIN(
    SELECT fbid, MAX(date_executed) AS latestDate
    FROM facebook_post_metrics
    GROUP BY fbid) t ON t.fbid = fpm.fbid AND t.latestDate = fpm.date_executed) fpm
ON fp.id = fpm.fbid;

Here is an updated SQL Fiddle example.