CODEWITHSUNDEEP
sqlsql-servert-sql
Adam Wróbel
Adam Wróbel

Reputation: 453

Select last value in each group

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|
|----------|--|

Upvotes: 0

Views: 100

Answers (3)

Atique Rahman
Atique Rahman

Reputation: 151

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 1

nayi224
nayi224

Reputation: 565

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

Upvotes: 4

Related Questions