CODEWITHSUNDEEP
mysqlsqlsqlite
NaturalBornCamper
NaturalBornCamper

Reputation: 3876

SQL GROUP BY while displaying the most recent date in each column, without subqueries

Given a table with this subset of rows:

POSTS TABLE
id      person_id      ig_post_date    fb_post_date
...
1           5               NULL        2018-03-24
2           5           2018-03-21         NULL
3           5           2018-03-22         NULL
4           5               NULL        2018-03-21
...

Is there a way to aggregate these results grouped by person_id and display the most recent Instagram post date AND most recent Facebook post date without using subqueries? I can't put my finger on the correct JOIN / GROUP BY / ORDER BY query to end up with this:

person_id      last_ig_post_date    last_fb_post_date
5                  2018-03-22          2018-03-24

Upvotes: 0

Views: 63

Answers (2)

Miguel Mota
Miguel Mota

Reputation: 84

I think you could use MAX, something like:

SELECT person_id,MAX(ig_post_date),MAX(fb_post_date) FROM mytable GROUP BY person_id

Upvotes: 1

kc2018
kc2018

Reputation: 1460

select person_id, 
max(ig_post_date) last_ig_post_date, 
max(fb_post_date) last_fb_post_date
from posts
group by person_id;

Upvotes: 3

Related Questions