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

134
March 29, 2018, at 10:08 AM

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
Answer 1
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;
Answer 2

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

Rent Charter Buses Company
READ ALSO
stored procedure returns null value

stored procedure returns null value

i am making a stored procedure to give cashback based on the total transactions by a single person a month but when called it shows null value for cashback get

138
MySQL insert with select on same table

MySQL insert with select on same table

I am trying to migrate to a new database and am trying to preserve the parent child hierarchy by saving the previous ID under a 'TMP' column the problem is when I try and pull the related ID by selecting said TMP column I get the MySQL Insert Query #1093 errorHere...

151
IF with SUM produces incorrect results in MySQL

IF with SUM produces incorrect results in MySQL

I am getting incorrect results when I try to insert a SUM into an IF clauseThe results are correct when I use COUNT, either incorrect or NULL when I use SUM

125
MySQL Creating a Case in a query based on top 100 results

MySQL Creating a Case in a query based on top 100 results

I'm trying to create a MySQL query based on the following criteria:

226