1

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 ?

0

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

If you are really only interested in getting back those 3 values, then you only need a GROUP BY combined with a MAX aggregate:

SELECT station_code, parameter_code, MAX(date_time) AS latest_update_time
  FROM station_data
 GROUP BY station_code, parameter_code
1
  • This makes sense now, didn't think of the GROUP BY clause. Works like a charm, thank you. Aug 13 '15 at 13:12
0

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)

1
  • This one generates the result but it is working forever (~15 minutes execution time) which is close to my non-optimal last update calculation. Aug 13 '15 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.