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.
rudy2024Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
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) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cyberkiwiCommented:
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
rudy2024Author Commented:
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.
rudy2024Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.