mysql select row based on distinct and latest timestamp

Question

I have my SQL data table for a sensor that is identified by a serial number and there are 4 output values for the sensor, that is 1,2,3,4.

I need to fetch the latest record for each sensor with the latest each output value(1,2,3,4)

Table

 id    serial    output_value           timestamp
  --    ------    ------------           ---------
   1     111           2            2020-12-1 01:00:06 
   2     111           2            2020-12-1 01:00:05 
   3     111           1            2020-12-1 01:00:04 
   4     111           3            2020-12-1 01:00:03 
   5     222           1            2020-12-1 01:00:02 
   6     222           3            2020-12-1 01:00:01 
   7     222           3            2020-12-1 01:00:00

I need to get the rows

 id    serial    output_value           timestamp
  --    ------    ------------           ---------
   1     111           2            2020-12-1 01:00:06 
   3     111           1            2020-12-1 01:00:04 
   4     111           3            2020-12-1 01:00:03 
   5     222           1            2020-12-1 01:00:02 
   6     222           3            2020-12-1 01:00:01

Command

SELECT DISTINCT ON (serial)
id,serial,output_value, timestamp  
FROM sensorTable
ORDER BY serial, timestamp DESC;

But I have no idea how to add output_value with it.

Mysql version

mysql 5.7.23, for Linux (x86_64) using  EditLine wrapper

mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper — CodeDezk, Jan 02 '21 at 08:33
SELECT DISTINCT ON is postgresql mysql does not have an equivalent. — P.Salmon, Jan 02 '21 at 08:59
If indeed you are using MySQL (I'm still not convinced) then, for further help see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query - but note that this is the most frequently asked question on SO, and, unlike below, it's sometimes answered correctly too. — Strawberry, Jan 02 '21 at 09:54
Since you seem to want only one record every time you have multiple rows with identical `serial` and `output_value` values, you can `GROUP BY` them. Once they're grouped, ask for the maximum timestamp: [fiddle](http://sqlfiddle.com/#!9/fca103/6) — Scratte, Jan 02 '21 at 18:08

Alex Collette · Answer 1 · 2021-01-02T18:34:47.420

0

--EDIT--

As pointed out by strawberry and Scratte, my previous answer was the wrong way to solve the problem. This is a better solution that does not rely on a hack.

SELECT 
    s.*
FROM
    sensors AS s
INNER JOIN
    (
    SELECT 
        serial, 
        output_value, 
        MAX(timestamp) AS timestamp
    FROM 
        sensors
    GROUP BY 
        serial, output_value
    ) AS sq
ON 
    s.serial = sq.serial
AND
    s.output_value = sq.output_value
AND
    s.timestamp = sq.timestamp

edited Jan 02 '21 at 18:34

answered Jan 02 '21 at 08:57

Alex Collette

1,664
13
26

@Strawberry, I'm not sure what you mean. – Alex Collette Jan 02 '21 at 17:27
Please post the correct way to do it then, or provide a reference to the correct way. It doesn't help anyone for you to insult me, and then move on. My answer might not be the best, but at least it is an answer. – Alex Collette Jan 02 '21 at 17:51
"You might have learned a proper way to do this rather than this disheartening hack!". Even if it was just meant as a criticism, you should post the correct solution. – Alex Collette Jan 02 '21 at 18:04
I think it would have been a little better to just ask for the max(timestamp) – Scratte Jan 02 '21 at 18:13
I think you're right @Scratte, although, it still wont let you get the id column, I think you would need to use a subquery to make that happen. – Alex Collette Jan 02 '21 at 18:28
Ive updated the solution to not require changes to the MySQL config – Alex Collette Jan 02 '21 at 18:35
You shouldn't mark your post with "EDIT". Just edit it to your best Answer. I've played with a [fiddle](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b258db1cd24dfecafcb6f3bede3af778). There are a lot of options. Also see the duplicate target for more ideas. But remember to give attribution, should you use one :) – Scratte Jan 02 '21 at 18:44

mysql select row based on distinct and latest timestamp

1 Answers1