Выберите значение столбца, где другой столбец является максимальным для группы

Я пытаюсь выбрать два столбца в таблице (идентификатор и состояние). В таблице должно отображаться состояние с максимальным значением для каждого идентификатора. Я пробовал несколько других примеров, но ничего не работает.

Исходная структура данных:

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

Полученная структура данных должна выглядеть следующим образом:

ID  STATE (Max Value)
1   FL
2   AL

Флорида и Алабама имеют наибольшие значения в своих идентификационных группах.

Любая помощь была бы весьма признательна по этому поводу. Я уже нашел SO-ответ здесь, но не смог заставить ответы работать для меня.

Ответ 1

Для SQL Server (и других продуктов с оконными функциями):

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

Ответ 2

Решение, основанное на предположении, что value является числовым:

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

Если несколько State с тем же ID имеют одинаковые value, все они получат одинаковый Rank. Это отличается от использования Row_Number, которые возвращают уникальные номера строк, но порядок выбирается произвольно. (См. Также: SQL RANK() против ROW_NUMBER())

Ответ 3

Вы можете использовать подзапрос, чтобы получить этот результат:

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

См. SQL Fiddle with Demo