mysql - using Max on two fields

Question

I am trying to run a query to find maximum pDate and maximum id from the dataset below:

id,pDate,CustomerID,refNum
1,2023-07-04,1000,980
2,2023-07-05,1000,982
3,2023-07-05,1001,984
4,2023-07-05,1001,985

output should be:

id,pDate,CustomerID,refNum
2,2023-07-05,1000,982
4,2023-07-05,1001,985

I have tried this query:

SELECT pDate, CustomerID,RefNum FROM table1 t1
JOIN (SELECT CustomerID, Max(pDate) as maxpdate FROM table1
GROUP BY CustomerID) sub on t1.CustomerID= sub.CustomerID AND t1.pDate = sub.maxdate

The above query is returning the following data:

id,pDate,CustomerID,refNum
2,2023-07-05,1000,982
3,2023-07-05,1001,984
4,2023-07-05,1001,985

Mysql Version is: 5.7.42

How can I achieve this?

Thanks!

Answer 1

Use correlated subquery with needed ORDER BY and LIMIT 1:

SELECT t1.*
FROM test t1
WHERE id = (
  SELECT id
  FROM test t2
  WHERE t1.CustomerID = t2.CustomerID
  ORDER BY pDate DESC, id DESC LIMIT 1
  )

id	pDate	CustomerID	refNum
2	2023-07-05	1000	982
4	2023-07-05	1001	985

fiddle