Select column value where other column is max of group
You can use a subquery to get this result:
select t1.id, t1.[state] MaxValuefrom yourtable t1inner join( select id, max(value) MaxVal from yourtable group by id) t2 on t1.id = t2.id and t1.value = t2.maxvalorder by t1.id
A solution, based on the assumption that value is numeric:
SELECT [ID], [State], [Value]FROM( SELECT [ID], [State], [Value], Rank() OVER (PARTITION BY [ID] ORDER BY [Value] DESC) AS [Rank] FROM [t1]) AS [sub]WHERE [sub].[Rank] = 1ORDER BY [ID] ASC, [State] ASCIf multiple States with the same ID have the same Value, they would all get the same Rank. This is different from using Row_Number, which return unique row numbers, but the order is chosen arbitrarily. (See also: SQL RANK() versus ROW_NUMBER())