Query using maximun date with its corresponding info

Question

The following customer table is given:

customerNumber  expireDate            versionNumber
111             2021-02-08 10:00:00       2
111             2022-02-08 10:00:00       2
111             2022-02-08 10:00:00       3
111             2023-02-08 10:00:00       2
111             2023-02-08 10:00:00       3
111             2023-02-08 10:02:00       2
111             2023-02-08 10:03:00       3

I have the following query I use to obtain the customer number with his maximun expire date with its corresponding version number:

SELECT customerNumber, MAX(expireDate), versionNumber 
FROM customers 
GROUP BY customerNumber;

I put the GROUP BY instruction because there could be several customerNumber numbers. It returns:

customerNumber   expireDate           versionNumber
111              2023-02-08 10:03:00       2    

Even though, that is not the correct output. The result should output:

customerNumber   expireDate           versionNumber
111              2023-02-08 10:03:00      3

How should the query be in order to obtain the customer number with his maximun expire date with its corresponding version number?

Answer 1

If you use Row_Number you can select the 1 row for each customer with the max expire date

WITH CTE AS
(
  SELECT customerNumber, expireDate, versionNumber, 
  row_number() OVER (PARTITION BY  customerNumber ORDER BY expireDate desc) as rn
  FROM customers 
)
SELECT customerNumber, expireDate, versionNumber
FROM CTE
WHERE rn=1

mariadb fiddle

MySQL fiddle

customerNumber	expireDate	versionNumber
111	2023-02-08 10:03:00	3