Select last value in each group

Question

In database I have two table. I want to join two tables and get last row each SensorType

Sensors table:

Id	CodeNumber	PortNumber	SensorType	IsActive
13	Dht11	3	0	1
14	Dht11	3	2	1
17	Global	100	4	1
18	Dht11	3	1	1
19	Dht11	3	3	1

SensorsValue table (SensorId is foreign key Sensors table):

Id	SensorId	Value	DateOfRetrevingValue
19	13	25	2021-07-23 08:50:27.0000000
20	14	45	2021-07-23 09:50:27.0000000
21	17	12	2021-07-23 10:50:27.0000000
22	18	24	2021-07-23 11:50:27.0000000
23	19	45	2021-07-23 12:50:27.0000000
24	13	23	2021-07-23 13:50:27.0000000
25	14	56	2021-07-23 14:50:27.0000000
26	17	23	2021-07-23 15:50:27.0000000
27	18	34	2021-07-23 16:50:27.0000000
28	19	23	2021-07-23 17:50:27.0000000

I want to join two tables and select SensorType from Sensors table and last Value (from SensorsValue) each SensorType. In this case i want get result:

|SensorType|Value|
|----------|-----|
|    0     | 23  |
|    2     | 56  |
|    4     | 23  |
|    1     | 34  |
|    3     | 13  |

I found this post and use this statement:

SELECT distinct Sensor.Type, MAX(SensorValues.Id), SensorValues.Value
FROM Sensor
INNER JOIN SensorValues ON Sensor.Id=SensorValues.SensorId
GROUP BY Sensor.Type

but I got error:

Column 'SensorValues.Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I use this statement without SensorValues.Value query work, but I got result:

|SensorType|Id|
|----------|--|

Select either MySQL or SQL Server, not both. Specify precise version also. Provide complete error message, not a part of it. — Akina, Jul 26 '21 at 09:28
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jul 26 '21 at 13:52

score 4 · Accepted Answer · answered Jul 26 '21 at 09:41

4

you should use first_value

select distinct t1.SensorType,
       first_value(t2.value) over(partition by t1.SensorType order by t2.DateOfRetrevingValue desc)
  from Sensors t1,
       SensorsValue t2
 where t1.id = t2.SensorId

answered Jul 26 '21 at 09:41

nayi224

555
2
6

Nice solution, but wouldn't be cleaner to just use `last_value` instead of `first_value`, so you won't need to order descendingly ?. – Marc Guillot Jul 26 '21 at 09:45
This is just my personal habit, first_value can replace last_value, using only one of them can make me more proficient. – nayi224 Jul 26 '21 at 09:59

Atique Rahman · Answer 2 · 2021-07-27T01:28:47.903

1

You can try this

SELECT Sensor.Type,  SensorValues.Value
FROM Sensor
INNER JOIN
(
    SELECT MAX(ID)Id, SensorId FROM SensorValues GROUP BY SensorId
)mId  ON Sensor.Id=mId.SensorId
INNER JOIN SensorValues ON mId.Id=SensorValues.Id

edited Jul 27 '21 at 01:28

answered Jul 26 '21 at 10:06

Atique Rahman

151
5

score 1 · Answer 3 · answered Jul 26 '21 at 13:53

This is a very handy place to use apply:

select s.*, sv.value, sv.DateOfRetrevingValue
from sensors s cross apply
     (select top (1) sv.*
      from SensorValues sv 
      where s.Id = sv.SensorId
      order by sv.DateOfRetrevingValue desc
     ) sv;

One advantage of this approach is that you can returns as many columns as you like. I also suspect that that performance will be better than a select distinct approach.

Select last value in each group

3 Answers3