mysql select on max date per item

Question

I have the following tables:

posts

| id | message         |
| 1  | a message       |
| 2  | another message |

posts_summaries

| post_id | datetime            | param    | value  | 
| 1       | 2016-06-06 09:01:09 | like     | 100    | 
| 1       | 2016-06-06 09:01:11 | reach    | 700    | 
| 1       | 2016-06-06 09:01:07 | comments | 80     | 
| 1       | 2016-06-06 10:01:06 | like     | 180    | 
| 1       | 2016-06-06 10:01:12 | reach    | 1200   | 
| 1       | 2016-06-06 10:01:09 | comments | 92     | 
| 2       | 2016-06-06 09:01:09 | like     | 94     | 
| 2       | 2016-06-06 09:01:11 | reach    | 600    | 
| 2       | 2016-06-06 09:01:07 | comments | 50     | 
| 2       | 2016-06-06 10:01:06 | like     | 133    | 
| 2       | 2016-06-06 10:01:12 | reach    | 1100   | 
| 2       | 2016-06-06 10:01:09 | comments | 93     | 

Every hour the current data from every post is stored in the post_summaries table. Now I want to load only the latest data from a post. So for post 2 that would be

| 2       | 2016-06-06 10:01:06 | like     | 133    | 
| 2       | 2016-06-06 10:01:12 | reach    | 1100   | 
| 2       | 2016-06-06 10:01:09 | comments | 93     | 

I tried to do this by using:

 select param, value, MAX(date) from posts_summaries WHERE post_id = 2 GROUP BY param

But this returns the oldest data with the newest date, so:

| 2       | 2016-06-06 10:01:06 | like     | 94     | 
| 2       | 2016-06-06 10:01:12 | reach    | 600    | 
| 2       | 2016-06-06 10:01:09 | comments | 50     | 

So it gives me the rows from 2016-06-06 09, But with date 2016-06-06 10.

What query can I use to get the latest data per param where post_id = 2

Thanks in advance!

Answer 1

One method is to use a where clause to get the last value for each parameter:

select ps.*
from post_summaries ps
where ps.post_id = 2 and
      ps.datetime = (select max(ps2.datetime)
                     from post_summaries ps2
                     where ps2.post_id = ps.post_id and ps2.param = ps.param
                    );

If you know that there are always three params and they are inserted together, then this is easier:

select ps.*
from post_summaries ps
where ps.post_id = 2
order by ps.datetime desc
limit 3;