MSSQL choosing row (from group) with max value
- by sriehl
I have a large database and am putting together a report of the data. I have aggregated and summed the data from many tables to get two tables that look like the following.
id | code | value id | code | value
13 | AA | 0.5 13 | AC | 2.0
13 | AB | 1.0 14 | AB | 1.5
14 | AA | 2.0 13 | AA | 0.5
15 | AB | 0.5 15 | AB | 3.0
15 | AD | 1.5 15 | AA | 1.0
I need to get a list of id's, with the code (sumed from both tables) with the largest value.
13 | AC
14 | AA
15 | AB
There are 4-6 thousand records and it is not possible to change the original tables. I'm not too worried about performance as I only need to run it a few times a year.