SQL Server aggregate function without group by-CodePudding

I want to include tcon.Inductive_Injection_Hours, tcon.Capacitive_Injection_Hours without applying group by. How can I do that?

SELECT 
    bp.Serial_Number, 
    tcon.Serial_Number AS ConverterSerialNumber, 
    MAX(tcon.Time_Stamp) AS DateStamp,  
    tcon.Inductive_Injection_Hours, 
    tcon.Capacitive_Injection_Hours
FROM 
    dbo.Bypass AS bp 
INNER JOIN 
    dbo.Converter AS c ON bp.Bypass_ID = c.Bypass_ID 
INNER JOIN 
    dbo.Converter_Tel_Data AS tcon ON c.Converter_ID = tcon.Converter_ID
WHERE 
    (bp.Site_ID = 7)
GROUP BY 
    bp.Serial_Number, tcon.Serial_Number, 
    tcon.Inductive_Injection_Hours, tcon.Capacitive_Injection_Hours
ORDER BY 
    ConverterSerialNumber

CodePudding user response：

I have figured it out.

select [data].Serial_Number,Time_Stamp,Inductive_Injection_Hours,Capacitive_Injection_Hours,b.Serial_Number from Converter_Tel_Data as [data]
inner join dbo.Converter AS c On [data].Converter_ID = c.Converter_ID
inner join dbo.Bypass as b on c.Bypass_ID = b.Bypass_ID
WHERE
  (Time_Stamp = (SELECT MAX(Time_Stamp) FROM Converter_Tel_Data WHERE Converter_ID = [data].Converter_ID)) And ([data].Site_ID=7)

 ORDER BY [data].Serial_Number

CodePudding user response：

You can use row_number - either in a CTE/derived table or using a trick with TOP 1.

Select Top 1 With Ties
       bp.Serial_Number
     , tcon.Serial_Number AS ConverterSerialNumber
     , tcon.Time_Stamp AS DateStamp
     , tcon.Inductive_Injection_Hours
     , tcon.Capacitive_Injection_Hours
  From dbo.Bypass AS bp 
 Inner Join dbo.Converter AS c On bp.Bypass_ID = c.Bypass_ID 
 Inner Join dbo.Converter_Tel_Data AS tcon ON c.Converter_ID = tcon.Converter_ID
 Where bp.Site_ID = 7
 Order By
       row_number() over(Partition By bp.Serial_Number Order By tcon.Time_Stamp desc)

This should return the latest row from the tconn table for each bp.Serial_Number.