CODEWITHSUNDEEP
mysqlsql
yongsheng
yongsheng

Reputation: 396

mySQL - Selecting the latest row from a table

I am using mySQL v5.7.29 MySQL Community Server (GPL)

I have a table where I create a unique index to avoid inserting duplicate values, on the following columns (all columns except the timestamp Updated) :

CREATE UNIQUE INDEX daily_unique ON daily(`Contract Date`, `Delivery`, `Product`, `Region`, `Price`)

I need to select the latest record by Updated ( timestamp ) .

I have tried the following:

SELECT * FROM daily
WHERE updated = ( SELECT max(Updated) FROM daily )

However, because there are multiple Delivery, Product, Region, the above does not work.

Here is an excerpt of the records:

     Contract Date    Delivery      Product Region   Price   Updated
0    2020-04-06       2020-04-01    A       B        1       2020-04-06 23:00:17
1    2020-04-06       2020-04-01    A       B        2       2020-04-07 10:30:16

I need the query to return the latest record only ( each set of Delivery, Product and Region should only return a single Contract Date and Price ).

     Contract Date    Delivery      Product Region   Price   Updated
1    2020-04-06       2020-04-01    A       B        2       2020-04-07 10:30:16

Upvotes: 0

Views: 56

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can also use correlated sub-query : 

SELECT d.*
FROM daily d
WHERE d.updated = (SELECT MAX(d1.updated)
                   FROM daily d1 
                   WHERE d.Delivery = d1.Delivery AND
                         d.Product  = d1.Product AND
                         d.Region   = d1.Region AND
                  );

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

If you are using MySQL 8+, then ROW_NUMBER is the way to go here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Delivery, Product, Region
                                 ORDER BY Updated DESC) rn
    FROM daily
)

SELECT `Contract Date`, Delivery, Product, Region, Price, Updated
FROM cte
WHERE rn = 1;

The row number above will count, starting at 1 with the most recently updated record, for each group of delivery/product/region records. Then, we query the CTE to retain only the latest record for each group.

Just in case you are using an earlier version of MySQL, here is another way to do it:

SELECT d1.`Contract Date`, d1.Delivery, d1.Product, d1.Region, d1.Price, d1.Updated
FROM daily d1
INNER JOIN
(
    SELECT Delivery, Product, Region, MAX(Updated) AS MaxUpdated
    FROM daily
    GROUP BY Delivery, Product, Region
) d2
    ON d1.Delivery = d2.Delivery AND
       d1.Product  = d2.Product AND
       d1.Region   = d2.Region AND
       d1.Updated  = d2.MaxUpdated;

Upvotes: 2

Related Questions