Mysql query to retrieve the latest updates based on DATETIME for each parameter I have a station_data table where sensors from different weather stations report their data.

Sample data:
id(AI),station_code,parameter_code,value,date_time
1,1,1,1.234,2015-01-01 00:00
2,2,4,2.345,2015-01-02 00:00
3,1,8,3.456,2015-01-03 00:00
4,1,9,4.567,2015-01-04 00:00
5,3,3,5.678,2015-01-05 00:00
6,4,2,6.789,2015-01-06 00:00


I am trying to get to this result:

id(AI),station_code,parameter_code,value,date_time
9000,1,1,1.234,2015-08-07 09:00
9001,1,2,2.345,2015-08-07 10:00
9002,1,3,3.456,2015-08-07 09:00
9003,1,4,4.567,2015-08-07 10:00
9004,1,5,5.678,2015-08-07 10:00
9005,1,6,6.789,2015-08-07 09:00
.
.
.
9998,10,1,1234.56,2015-08-07 09:00
9999,10,2,2345.67,2015-08-07 09:00


The result should contain every pair station_code / parameter code once and the corresponding latest update time.

At the moment I am running this query:

SELECT DISTINCT station_code,parameter_code 
FROM station_data 
ORDER BY station_code,parameter_code ASC;


which results to a list of the station and parameter codes being monitored and then I chain query the database (in a for loop) to select the latest update time from the above list.

SELECT * FROM station_data 
WHERE parameter_code=X AND station_code=X
ORDER BY date_time DESC LIMIT 1;


But this way of getting the data is nowhere next to optimum. Chain querying the database overloads the mysql server for a few minutes. 

Is there any way to get the same result set by using a more efficient single query ?
I think you may be looking for something like this:

SELECT s.id,s.station_code,s.parameter_code,s.value,s.date_time
  FROM station_data s
 WHERE s.date_time = (SELECT MAX(date_time) FROM station_data WHERE station_code = s.station_code AND paramter_code = s.parameter_code)


Add whichever parameters are important for uniqueness in your sub-query.

I would also recommend for this to run optimally, you create an index that indexes the three columns in that index, namely date_time,station_code and parameter_code)