1

I have a table called "history" with some sample data below:

SITE_ID TARGET_PRIMARY_KEY TARGET_TABLE UPDATED_AT         USER_ID
1             1025test 4             2018/10/25 12:01 e4
1             1025test 4             2018/10/25 12:02 e1
1             1025test 4             2018/10/25 12:03 e2
1             1025test 4             2018/10/25 12:04 e3
1             aaa         4             2018/10/19 15:01 e1
5             aaa         7             2018/10/19 15:02 e2
1             aaa         4             2018/10/19 15:03 e3
1             aaa         4             2018/10/19 15:04 e4
1             aaa         4             2018/10/19 15:05 e5
1             aaa         4             2018/10/19 15:06 e6

What I want to achieve is: For each unique combination of "SITE_ID" and "TARGET_PRIMARY_KEY", pick up the record that has the greatest (latest) value of "UPDATED_AT" and also make sure the corresponding "USER_ID" is correct.

Expecting result is:

1 1025test 4 2018/10/25 12:04 e3
1 aaa         4 2018/10/19 15:06 e6

I have tried the following sql:

    SELECT 
        hist.SITE_ID AS siteId,
        TARGET_PRIMARY_KEY AS skey,
        hist.TARGET_TABLE AS targetTable,
        MAX(UPDATED_AT) AS lastActionDate,
        hist.USER_ID
    FROM
        history AS hist
    WHERE
        hist.TARGET_TABLE = 4
            AND hist.SITE_ID = 1
    GROUP BY TARGET_PRIMARY_KEY

However, the "USER_ID" information seems not synced with the "UPDATED_AT" I got:

1 1025test 4 2018/10/25 12:04 e4

Can somebody please give me some advise? Thanks.

Gauravsa
  • 6,330
  • 2
  • 21
  • 30
zaozaoer
  • 361
  • 1
  • 3
  • 14
  • Possible duplicate of [how do I query sql for a latest record date for each user](https://stackoverflow.com/questions/2411559/how-do-i-query-sql-for-a-latest-record-date-for-each-user) – Nick Nov 21 '18 at 04:54

1 Answers1

1

I would use a correlated subquery for this:

select h.*
from history h
where h.updated_at = (select max(h2.updated_at)
                      from history h2
                      where h2.site_id = h.site_id and
                            h2.target_primary_key = h.target_primary_key
                     );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you. Using Correlated subquery solved the issue!! – zaozaoer Nov 21 '18 at 04:03
  • The same where should be included in outer select otherwise rows matched to other site/pk would be selected. – fifonik Nov 21 '18 at 04:05
  • @fifonik I don't think its necessary. Reason: ① physically, h2 is not accessable from outer select. ② the inner where conditions have already connected an outer select entry with its corresponding inner select entry. – zaozaoer Nov 21 '18 at 09:45
  • If there are more than one record with the same updated_dt value returned from inner table it woud be included in results or outer table regardless site_id & target_primary_key values. – fifonik Nov 21 '18 at 20:38