How to fetch last record for each day in a given group of records?

Question

I have a table with the following structure:

 id | service_name | subscribers_count | date
 1  | service 1    | 1                 | 2017-07-01 01:01:01
 2  | Service 1    | 2                 | 2017-07-01 01:01:02
 3  | Service 1    | 3                 | 2017-07-02 01:01:01
 4  | Service 1    | 2                 | 2017-07-03 01:01:01
 5  | Service 1    | 3                 | 2017-07-04 01:01:01
 6  | Service 2    | 1                 | 2017-07-01 01:01:01
 7  | Service 2    | 2                 | 2017-07-01 01:01:02
 8  | Service 2    | 3                 | 2017-07-01 01:01:03
 9  | Service 2    | 4                 | 2017-07-02 01:01:01

It's easy to fetch the last record for each service, but I need more - I need to fetch the last record for each service for each day. This is what I expect to get:

2  | Service 1    | 2                 | 2017-07-01 01:01:02
3  | Service 1    | 3                 | 2017-07-02 01:01:01
4  | Service 1    | 2                 | 2017-07-03 01:01:01
5  | Service 1    | 3                 | 2017-07-04 01:01:01
8  | Service 2    | 3                 | 2017-07-01 01:01:03
9  | Service 2    | 4                 | 2017-07-02 01:01:01

What's the most effective way to write it?

@Strawberry here you are https://stackoverflow.com/questions/10999522/how-to-get-the-latest-record-in-each-group-using-group-by — CorrieSparrow, Jul 11 '17 at 11:01
I can't see how the 'hard' part is any harder than the 'easy' part ;-) — Strawberry, Jul 11 '17 at 12:37

Gordon Linoff · Answer 1 · 2017-07-12T03:27:44.643

1

One method is a correlated subquery in the where clause:

select t.*
from t
where t.date = (select max(t2.date)
                from t t2
                where t2.service_name = t.service_name and
                      date(t2.date) = date(t.date)
               );

edited Jul 12 '17 at 03:27

answered Jul 11 '17 at 10:43

Gordon Linoff

1,242,037
58
646
786

(service_name) - although I agree it should be an id – Strawberry Jul 11 '17 at 12:54

Strawberry · Answer 2 · 2017-07-11T12:56:17.063

To use your own solution...

SELECT t1.* 
  FROM messages t1
  JOIN 
     ( SELECT from_id
            , SOMETHING HERE
            , MAX(timestamp) timestamp 
         FROM messages 
        GROUP 
           BY from_id
            , SOMETHING HERE
     ) t2
    ON t1.from_id = t2.from_id 
   AND SOMETHING HERE
   AND t1.timestamp = t2.timestamp;

This solution is extremely 'effective'. Although you'd only appreciate any difference between this solution and Gordon's on quite a sizeable data set, I suspect that this solution will out-perform that one - although I'd be delighted to be proven wrong.

How to fetch last record for each day in a given group of records?

2 Answers2