Richard Todd Richard Todd - 1 month ago 7
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.

Thanks
Richard

Answer

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