1

I have a table with columns document_key (varchar(100)), last_modified (datetime), modified_by (varchar(100)), product_key (char(36)), service_type (char(20)).

How can I extract the most recent entry (last_modified) for each document_key, with a request that is compatible with MySQL 5.6 AND above ?

************************************************************************************************
* id   * document_key     * last_modified          * modified_by  * product_key * service_type *
************************************************************************************************
* 000a * aaa-aaa-aaa-aaa  * 2020-01-23 08:00:00    * Admin        * 123-456     * service      *
************************************************************************************************
* 000b * aaa-aaa-aaa-aaa  * 2020-01-20 23:00:00    * Admin        * 123-456     * service      *
************************************************************************************************
* 000c * bbb-bbb-bbb-bbb  * 2020-01-23 12:00:00    * Admin        * 123-456     * service      *
************************************************************************************************
* 000d * bbb-bbb-bbb-bbb  * 2020-01-18 04:00:00    * Admin        * 123-456     * service      *
************************************************************************************************

I want to get the lines with id = 000a and 000c.

I had this request with MySQL 5.6 and it worked. However MySQL 8 does not return the most recent entry.

SELECT document_key, last_modified, modified_by 
FROM (SELECT document_key, last_modified, modified_by FROM document_modification_traces WHERE product_key = '123-456' 
    AND service_type = 'service' ORDER BY last_modified DESC) sub_table 
GROUP BY document_key;
Pierre
  • 41
  • 6

2 Answers2

2

You can use correlated subquery -

SELECT document_key, last_modified, modified_by 
FROM tablename a
where  last_modified = (SELECT max(last_modified) from tablename b where a.document_key=b.document_key) and product_key = '123-456' 
    AND service_type = 'service'
Fahmi
  • 37,315
  • 5
  • 22
  • 31
1

One canonical way of doing this, which would work on all versions of MySQL, uses a join:

SELECT d1.*
FROM document_modification_traces d1
INNER JOIN
(
    SELECT document_key, MAX(last_modified) AS max_last_modified
    FROM document_modification_traces
    GROUP BY document_key
) d2
    ON d1.document_key = d2.document_key AND
       d1.last_modified = d2.max_last_modified;

To make the above query run even faster, consider adding the following index:

CREATE INDEX idx ON document_modification_traces (document_key, last_modified);

This might dramatically speed up the inner join.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360