Richard Todd Richard Todd - 1 year ago 73
SQL Question

SQL Select column value where other column is max of group

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 Source

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

     ROW_NUMBER() OVER (PARTITION BY ID ORDER BY value desc) as rn
) t
   t.rn = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download