SQL: выбрать только строки с максимальным значением в столбце

Вы можете сделать это, используя IN. Попробуйте следующий запрос:

SELECT * 
FROM t1 WHERE (id, rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

На мой взгляд, это менее сложно... легче читать и поддерживать.

Я поражен тем, что ни один из ответов не предложил решение с использованием оконных функций SQL:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) ranked_order
          FROM YourTable) a
 WHERE a.ranked_order = 1 

Оконные функции были добавлены в стандарт SQL ANSI/ISO Standard SQL:2003, а затем расширены с помощью ANSI/ISO Standard SQL:2008. В настоящее время они доступны у всех основных поставщиков СУБД. Существует несколько типов функций ранжирования, которые могут помочь в случае возникновения ничьих: RANK, DENSE_RANK, PERCENT_RANK.

Еще одно решение — использовать коррелированную подзапрос:

SELECT yt.id, yt.rev, yt.contents
FROM YourTable yt
WHERE rev = 
    (SELECT MAX(rev) FROM YourTable st WHERE yt.id = st.id)

Наличие индекса на (id, rev) делает подзапрос почти таким же, как простой поиск...

Ниже приведены сравнения с решениями из ответа @AdrianCarneiro (подзапрос, левое соединение) на основе измерений в MySQL с таблицей InnoDB объемом ~1 миллион записей, размер группы: от 1 до 3.

Для полных сканирований таблиц времена выполнения подзапроса/левого соединения/коррелированной таблицы соотносятся как 6/8/9. Однако, когда речь идет о прямых поисках или пакетах (id IN (1,2,3)), подзапрос значительно медленнее, чем другие (из-за повторного выполнения подзапроса). Я не смог выделить разницу в скорости между левым соединением и коррелированными решениями.

Одно последнее замечание: так как левое соединение создает n*(n+1)/2 соединений в группах, его производительность может значительно зависеть от размера групп...

Я не могу гарантировать производительность, но вот один трюк, вдохновлённый ограничениями Microsoft Excel. Он обладает несколькими полезными функциями.

ПОЛЕЗНЫЕ МОМЕНТЫ

• Этот подход заставляет вернуть только одну "максимальную запись", даже если есть ничья (иногда это полезно).
• Не требует объединения таблиц (JOIN).

ПОДХОД

Выполнять это немного неудобно, и вам нужно знать диапазон допустимых значений для столбца rev. Предположим, что мы знаем, что значения rev — это числа от 0.00 до 999, включая десятичные дроби, при этом всегда будет только две цифры после запятой (например, 34.17 — это допустимое значение).

Суть заключается в том, что вы создаёте один синтетический столбец, объединяя первичное поле сравнения и данные, которые вам нужны. Таким образом, вы можете заставить агрегатную функцию SQL MAX() вернуть все данные (поскольку они упакованы в один столбец). Затем вам нужно будет распаковать данные.

Вот как это выглядит с приведённым выше примером на SQL:

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

Упаковка начинается с того, что мы заставляем столбец rev иметь известную длину символов, независимо от значения. Например:

• 3.2 станет 1003.201
• 57 станет 1057.001
• 923.88 станет 1923.881

Если вы сделаете это правильно, то строчное сравнение двух чисел должно дать тот же "максимум", что и числовое сравнение этих двух чисел, а вернуться к первоначальному числу можно с помощью функции SUBSTRING (которая доступна в том или ином виде практически везде).

Другой способ решения задачи — использование аналитической функции MAX() в предложении OVER PARTITION:

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

Другой вариант с использованием ROW_NUMBER() в предложении OVER PARTITION, который уже задокументирован в этом посте, выглядит следующим образом:

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

Оба запроса работают корректно в Oracle 10g.

Обратите внимание, что решение с MAX() работает, безусловно, БЫСТРЕЕ, чем решение с ROW_NUMBER(), поскольку сложность MAX() равна O(n), в то время как сложность ROW_NUMBER() в минимальном случае составляет O(n.log(n)), где n — это количество записей в таблице!