asked on

How to get last record of each group

Hi Experts,
I am using a MySQL database and I have a couple of tables, one for measurements and another for last measured values. When I insert data into the measurement table I usually do bulk inserts and I need help figuring out the last measured values inserted.

The following example will clarify what I am trying to do.

Table that contains all the measurements:

CREATE TABLE measurement(
meas_id INT AUTO_INCREMENT,
sensor_id INT,
meas_type INT,
meas_val FLOAT,
meas_date_time TIMESTAMP
);

Table that contains the last measurement:

CREATE TABLE last_measurement(
sensor_id INT,
meas_type INT,
meas_val FLOAT,
meas_date_time TIMESTAMP,
PRIMARY KEY ('sensor_id', 'meas_type')
);

Step 1: Get meas_id from measurement table (first_id)
Step 2: Bulk insert a bunch of measurements
Step 3: Get meas_id from measurement table (last_id)

Then I need to get the records that contain the most recent measurement for the combination of sensor_id, meas_type.

To get the max date I can use:
SELECT sensor_id, meas_type, MAX(meas_date_time) FROM measurement
WHERE meas_id >= first_id AND meas_id <= last_id;

But I don't know how to include meas_val on that query.

I tried:
SELECT sensor_id, meas_type, MAX(meas_date_time), meas_val FROM measurement
WHERE meas_id >= first_id AND meas_id <= last_id;

But the meas_val does not correspond to the max(meas_date_time) it corresponds to the first record.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

I'm not sure why you have first_id and last_id in there.
If you just wanted, for each sensor_id and meas_type, the latest measurement, then

select m.sensor_id, m.meas_type, n.meas_date_time, n.meas_val
from (
      SELECT sensor_id, meas_type, MAX(meas_date_time) MaxDate
      FROM measurement
      group by sensor_id, meas_type) m
inner join measurement n on m.sensor_id=n.sensor_id
      and m.meas_type=n.meas_type and m.MaxDate=n.meas_date_time

rudy2024

ASKER

Those id's are to limit the search to a small subset of the last records inserted using bulk insert.
I am still verifying your solution. It returned an empty set so I am checking if I typed something wrong.

rudy2024

ASKER

Thank you for your help. I just had to add a GROUP BY to get it working.

select m.sensor_id, m.meas_type, n.meas_date_time, n.meas_val
from (
SELECT sensor_id, meas_type, MAX(meas_date_time) MaxDate
FROM measurement
WHERE meas_id >= first_id AND meas_id <= last_id
GROUP BY sensor_id, meas_type) m
inner join measurement n on m.sensor_id=n.sensor_id
and m.meas_type=n.meas_type and m.MaxDate=n.meas_date_time