SELECT set of most recent id, amount FROM table, where id occurs many times
- by Jon Cram
I have a table recording the amount of data transferred by a given service on a given date. One record is entered daily for a given service.
I'd like to be able to retrieve the most recent amount for a set of services.
Example data set:
serviceId | amount | date
-------------------------------
1 | 8 | 2010-04-12
2 | 11 | 2010-04-12
2 | 14 | 2010-04-11
3 | 9 | 2010-04-11
1 | 6 | 2010-04-10
2 | 5 | 2010-04-10
3 | 22 | 2010-04-10
4 | 17 | 2010-04-19
Desired response (service ids 1,2,3):
serviceId | amount | date
-------------------------------
1 | 8 | 2010-04-12
2 | 11 | 2010-04-12
3 | 9 | 2010-04-11
Desired response (service ids 2, 4):
serviceId | amount | date
-------------------------------
2 | 11 | 2010-04-12
4 | 17 | 2010-04-19
This retrieves the equivalent as running the following once per serviceId:
SELECT serviceId, amount, date
FROM table
WHERE serviceId = <given serviceId>
ORDER BY date DESC
LIMIT 0,1
I understand how I can retrieve the data I want in X queries. I'm interested to see how I can retrieve the same data using either a single query or at the very least less than X queries.
I'm very interested to see what might be the most efficient approach. The table currently contains 28809 records.
I appreciate that there are other questions that cover selecting the most recent set of records. I have examined three such questions but have been unable to apply the solutions to my problem.
