CODEWITHSUNDEEP
sql
Shivam Sachaphimukh
Shivam Sachaphimukh

Reputation: 263

Using MAX within INNER JOIN - SQL

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

Upvotes: 17

Views: 103648

Answers (3)

Spock
Spock

Reputation: 4900

Untested, but you can do it by simply selecting the Max Date for the FBID in question. Something like this...

SELECT fb.name, fpm.number, fpm.FBID, fpm.date_executed, fpm.user_executed
FROM facebook_post_metrics fpm
    join facebook_posts fb ON fpm.fbid = fb.id AND fb.updated_at = fpm.date_executed
WHERE fpm.Date_Executed = (
SELECT MAX(f.Date_executed)
FROM facebook_post_metrics f
WHERE f.FBID = fpm.fbid)

Here a fiddle http://sqlfiddle.com/#!2/1485d/15

Upvotes: 1

AdamMc331
AdamMc331

Reputation: 16690

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.

Upvotes: 25

radar
radar

Reputation: 13425

you need a subquery that calculated the max using group by and then join again with same tables to get all the details

As per the latest edit, the update_at column is no longer there in posts as there are two entries, you can get only one by doing a group by

if you want all the entries then remove the aggregation and group by in the outer query.

select max(fp.name), fpm.number, fpm.FBID, fpm.date_executed,
       fpm.user_executed
from facebook_posts fp
join ( select max(Date_executed) dexecuted, FBID
       from facebook_post_metrics 
       group by FBID
      ) t
on fp.id = t.fbid
join facebook_post_metrics fpm
on fpm.fbid = t.fbid
and fpm.date_executed = t.dexecuted
group by fpm.number, fpm.FBID, fpm.date_executed,
       fpm.user_executed

Upvotes: 2

Related Questions