How do I return the most recent Date(datetime) row, within Group BY without LIMIT-CodePudding

I have an event which runs a procedure to calculate a profit/loss and insert once a day.

Sometimes I must re-run this manually if there are amendments required.

How do I retrieve the list for the latest record each day?

The records look like this:

datePnL	profitLoss
2022-09-27 09:04:27	3176615
2022-09-27 08:33:16	3423206
2022-09-27 08:00:00	2999125
2022-09-26 08:00:00	3403436
2022-09-23 08:00:00	3523200

The following query:

SELECT pNLdate, profitLoss
FROM totalPL
GROUP BY DATE(pNLdate)
ORDER BY pNLdate DESC

Returns:

datePnL	profitLoss
2022-09-27 08:00:00	2999125
2022-09-26 08:00:00	3403436
2022-09-23 08:00:00	3523200

Desired is:

datePnL	profitLoss
2022-09-27 09:04:27	3176615
2022-09-26 08:00:00	3403436
2022-09-23 08:00:00	3523200

CodePudding user response：

Please forgive if a typo here messes up this query, but basically you can restrict your results to the most recent one for each day by making a result list of the last result for each day and joining to it. I just did the following for one of my own tables, and then changed it to your table/column names, hence the chance for a typo.

select pNLdate, profitLoss
from
   (select max(a.datePnL) lastDatePnL from totalPL a group by date(a.datePnL)) b
join
   totalPL c
 on b.lastDatePnL = c.datePnL
order by c.pNLdate desc