Select column value where other column is max of group

Question

I am trying to select two columns into a table (ID and state). The table should show the state with the maximum value for each ID. I've tried a few other examples but nothing seems to work.

Original data structure:

ID  state    value (FLOAT)
1   TX   921,294,481 
1   SC   21,417,296 
1   FL   1,378,132,290 
1   AL   132,556,895 
1   NC   288,176 
1   GA   1,270,986,631 
2   FL   551,374,452 
2   LA   236,645,530 
2   MS   2,524,536,050 
2   AL   4,128,682,333 
2   FL   1,503,991,028

The resulting data structure should therefore look like this:

ID  STATE (Max Value)
1   FL
2   AL

Florida and Alabama having the largest values in their ID groups.

Any help would be greatly appreciated on this. I did find a SO answer here already, but could not make the answers work for me.

So are you also using `SQL-Server`, if so, which version? Is `value` a `varchar`? — Tim Schmelter, Jan 10 '13 at 09:47

score 40 · Accepted Answer · answered Jan 10 '13 at 09:53

40

For SQL Server (and other products with windowed functions):

SELECT *
FROM
(
   SELECT
     *,
     ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value desc) as rn
   FROM
     UnnamedTable
) t
WHERE
   t.rn = 1

answered Jan 10 '13 at 09:53

Damien_The_Unbeliever

234,701
27
340
448

Thank you Sir. Other solutions here from Jacco also work and is a good answer. – Richard Todd Jan 10 '13 at 11:56

score 8 · Answer 2 · answered Jan 10 '13 at 10:43

You can use a subquery to get this result:

select t1.id, t1.[state] MaxValue
from yourtable t1
inner join
(
  select id, max(value) MaxVal
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.value = t2.maxval
order by t1.id

See SQL Fiddle with Demo

score 7 · Answer 3 · edited May 23 '17 at 11:47

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] = 1
ORDER BY
  [ID] ASC,
  [State] ASC

If 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())

Select column value where other column is max of group

3 Answers3