SQL inner join with top 1

sql, sql-server-2008, t-sql

Solution

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

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 latest `[TimeStampUtc]` .

Note that: I don't know about the tables' structures you are using, but you might need to use another column to group by in the `PARTITION BY` clause instead of `MeterReadingId`.

Problem

Hi I need to get table of latest 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 ``` off course now i got all values which belong to inputs of name 'DayYield and plantId =1 My question is how can got table only of those values which have latest [TimeStampUtc] Other words: get all those [Val] which belong to input of name 'DayYield and plantId =1 and are last inserted into table.

Original source