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.

Answer 1

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 

Answer 2

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