Skip to content
Advertisement

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

facebook_post_metrics looks like

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:

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:

Advertisement

Answer

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:

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

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

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:

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

Here is an updated SQL Fiddle example.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement