SQL inner join with vertex 1

Question

Hi, I need to get a table of the last known value, one for each input.

I started with this

SELECT [MeterReadingId] ,[TimeStampUtc] ,[Val] ,[MeterReading].[InverterInputId] ,[Status] FROM [Project].[dbo].[MeterReading] inner join InverterInput on [MeterReading].InverterInputId = [InverterInput].InverterInputId inner join Inverter on [InverterInput].InverterId = [Inverter].InverterId where [InverterInput].InputName = 'DayYield' and [Inverter].PlantId = 1 

From the course, now I got all the values ​​that belong to the inputs with the name "DayYield" and "plantId = 1" My question is: how to get a table of only those values ​​that have the latest [TimeStampUtc]

Other words: get all those [Val] that belong to the input of the name "DayYield" and "plantId = 1" and are inserted last into the table.

Answer 1

For this you can use the ranking functions , ROW_NUMBER() , for example. Something like that:

 WITH CTE AS ( SELECT [MeterReadingId] ,[TimeStampUtc] ,[Val] ,m.[InverterInputId] ,[Status] ,ROW_NUMBER() OVER(PARTITION BY m.InverterInputId ORDER BY m.[TimeStampUtc] DESC) AS RN FROM [Project].[dbo].[MeterReading] AS m inner join InverterInput AS ii on m.InverterInputId = ii.InverterInputId inner join Inverter AS i on ii.InverterId = i.InverterId where ii.InputName = 'DayYield' and i.PlantId = 1 ) SELECT * FROM CTE WHERE RN = 1; 

Using ORDER BY m.[TimeStampUtc] DESC will give you the last [TimeStampUtc] .

Note: I do not know about the table structures you use, but you may need to use a different column to group in PARTITION BY instead of MeterReadingId . Strike>

Answer 2

One parameter is cross apply , for example:

 select * from Devices d cross apply ( select top 1 * from Measurements m where m.device_id = d.id order by m.timestamp desc ) m 

Answer 3

I found that the cross apply function is much easier to read. It also means that you maintain the basic structure of a regular request.

 update cc set cc.OutCC = oocd.CC, cc.Outcel = oocd.cel, cc.Outcd = oocd.cd, cc.Outrn = oocd.rn, cc.outidate = oocd.idate, cc.outtdate = oocd.tdate from @CbC cc cross apply ( select top 1 * from @OutC ocd where substring(ocd.rn,LEN(ocd.rn) - 9,9) = substring(cc.rn,LEN(cc.rn) - 9,9) and (ocd.cdate between cc.idate and DATEADD(hh,1,cc.idate)) and ocd.cdate < cc.tdate and ocd.cel like '%' + cc.line + '%' collate database_default order by ocd.idate ) oocd