С первого взгляда...

Все, что вам нужно, это предложение GROUP BY с агрегатной функцией MAX:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Никогда не бывает так просто, не так ли?

Я только что заметил, что вам также нужен столбец content.

Это очень распространенный вопрос в SQL: найти все данные для строки с некоторым максимальным значением в столбце для определенного идентификатора группы. Я много слышал об этом за свою карьеру. Собственно, это был один из вопросов, на который я ответил на техническом собеседовании на моей нынешней работе.

На самом деле, это настолько распространено, что сообщество StackOverflow создало один тег только для решения подобных вопросов: наибольшее число на группу.

По сути, у вас есть два подхода к решению этой проблемы:

Присоединение с помощью простого group-identifier, max-value-in-group подзапроса

В этом подходе вы сначала находите group-identifier, max-value-in-group (уже решенное выше) в подзапросе. Затем вы присоединяете свою таблицу к подзапросу с равенством по group-identifier и max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Левое объединение с самообслуживанием, настройка условий и фильтров соединения

При таком подходе вы остались за столом сами с собой. Равенство входит в group-identifier. Затем 2 умных хода:

1. Второе условие соединения имеет левое значение меньше правого.
2. Когда вы выполняете шаг 1, строки, которые фактически имеют максимальное значение, будут иметь NULL в правой части (это LEFT JOIN, помните?). Затем мы фильтруем объединенный результат, показывая только те строки, правая часть которых равна NULL.

Итак, вы получите:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Заключение

Оба подхода дают одинаковый результат.

Если у вас есть две строки с max-value-in-group для group-identifier, обе строки будут в результате в обоих подходах.

Оба подхода совместимы с SQL и ANSI, поэтому будут работать с вашей любимой СУБД, независимо от ее «вкуса».

Оба подхода также удобны для производительности, однако ваш опыт может варьироваться (СУБД, структура БД, индексы и т. Д.). Поэтому, когда вы предпочитаете один подход другому, используйте эталонный тест. И убедитесь, что вы выбрали тот, который вам больше всего подходит.

Я предпочитаю использовать как можно меньше кода ...

Вы можете сделать это с помощью 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) rank
          FROM YourTable) a
 WHERE a.rank = 1 

Добавлен в стандарт SQL ANSI / ISO Standard SQL: 2003 и позже расширен стандартом ANSI / ISO SQL: 2008, оконные (или оконные) функции теперь доступны у всех основных поставщиков. Есть и другие типы функций ранжирования, позволяющие решить проблему ничьей: RANK, DENSE_RANK, PERSENT_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)), подзапрос выполняется намного медленнее, чем другие (из-за повторного выполнения подзапроса). Однако я не мог различить левое соединение и коррелированные решения по скорости.

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

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

ХОРОШИЕ МАТЕРИАЛЫ

• Он должен принудительно возвращать только одну "максимальную запись", даже если есть ничья (иногда полезно)
• Это не требует соединения

ПОДХОД

Это немного некрасиво и требует, чтобы вы что-то знали о диапазоне допустимых значений столбца 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 задается число известной длины символа независимо от значения rev. так что например

• 3,2 становится 1003,201
• 57 становится 1057.001
• 923,88 становится 1923,881

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

Уникальные идентификаторы? Да! Уникальные идентификаторы!

Один из лучших способов разработки базы данных MySQL - это есть каждый id AUTOINCREMENT (Источник MySQL.com). Это дает множество преимуществ, которых здесь невозможно описать. Проблема с вопросом в том, что в его примере есть повторяющиеся идентификаторы. Это игнорирует эти огромные преимущества уникальных идентификаторов и в то же время сбивает с толку тех, кто уже знаком с этим.

Самое чистое решение

DB Fiddle

Новые версии MySQL поставляются с ONLY_FULL_GROUP_BY включенным по умолчанию, и многие из решений здесь терпят неудачу при тестировании с этим условием.

Даже в этом случае мы можем просто выбрать DISTINCT someuniquefield, MAX( whateverotherfieldtoselect ), ( * somethirdfield ) и т. Д. И не беспокоиться о том, чтобы понять результат или как работает запрос:

SELECT DISTINCT t1.id, MAX(t1.rev), MAX(t2.content)
FROM Table1 AS t1
JOIN Table1 AS t2 ON t2.id = t1.id AND t2.rev = (
    SELECT MAX(rev) FROM Table1 t3 WHERE t3.id = t1.id
)
GROUP BY t1.id;

• SELECT DISTINCT Table1.id, max(Table1.rev), max(Table2.content): Вернуть DISTINCT какое-то поле, MAX() какое-то другое поле, последнее MAX() является избыточным, потому что я знаю, что это всего одна строка, но она требуется по запросу.
• FROM Employee: поиск по таблице.
• JOIN Table1 AS Table2 ON Table2.rev = Table1.rev: Присоединяйтесь ко второй таблице к первой, потому что нам нужно получить комментарий max (table1.rev).
• GROUP BY Table1.id: Принудительно использовать сортированную по верхнему краю строку Salary каждого сотрудника как возвращаемый результат.

Обратите внимание, что, поскольку контент был ... в вопросе OP, нет возможности проверить, что это работает. Итак, я изменил это на ..a, ..b, так что теперь мы действительно можем видеть, что результаты верны:

id  max(Table1.rev) max(Table2.content)
1   3   ..d
2   1   ..b

Почему он чистый? DISTINCT(), MAX() и т. д. - все они прекрасно используют индексы MySQL. Это будет быстрее. Или это будет намного быстрее, если у вас есть индексирование, и вы сравните его с запросом, который просматривает все строки.

Исходное решение

Если ONLY_FULL_GROUP_BY отключен, мы можем по-прежнему использовать GROUP BY, но тогда мы используем его только для зарплаты, а не для идентификатора:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;

• SELECT *: вернуть все поля.
• FROM Employee: поиск по таблице.
• (SELECT *...) подзапрос: вернуть всех людей, отсортированных по зарплате.
• GROUP BY employeesub.Salary: принудительно использовать сортированную по верхнему краю строку Salary каждого сотрудника как возвращаемый результат.

Решение Unique-Row

Обратите внимание на определение реляционной базы данных: каждая строка в таблице имеет свой уникальный ключ. Это означало бы, что в примере вопроса id должен быть уникальным, и в этом случае мы можем просто сделать:

SELECT *
FROM Employee
WHERE Employee.id = 12345
ORDER BY Employee.Salary DESC
LIMIT 1

Надеюсь, это решение, которое решит проблему и поможет всем лучше понять, что происходит в БД.

Что-то вроде этого?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev
    FROM yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

Другой способ выполнить эту работу - использовать аналитическую функцию 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 

Эти 2 SELECT хорошо работают на Oracle 10g.

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

Мне нравится использовать решение этой проблемы на основе NOT EXIST:

SELECT 
  id, 
  rev
  -- you can select other columns here
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

Это выберет все записи с максимальным значением в группе и позволит вам выбрать другие столбцы.

Третье решение, о котором я почти никогда не упоминал, относится к MySQL и выглядит так:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Да, это выглядит ужасно (преобразование в строку и обратно и т. Д.), Но, по моему опыту, это обычно быстрее, чем другие решения. Возможно, это только для моих случаев использования, но я использовал его в таблицах с миллионами записей и множеством уникальных идентификаторов. Возможно, это потому, что MySQL довольно плохо оптимизирует другие решения (по крайней мере, за те 5,0 дней, когда я придумал это решение).

Важным моментом является то, что GROUP_CONCAT имеет максимальную длину строки, которую он может создать. Вероятно, вы захотите поднять этот предел, установив переменную group_concat_max_len. И имейте в виду, что это будет предел масштабирования, если у вас большое количество строк.

В любом случае, вышеуказанное не работает напрямую, если ваше поле содержимого уже текстовое. В этом случае вы, вероятно, захотите использовать другой разделитель, например, \ 0. Вы также быстрее достигнете group_concat_max_len лимита.

Я думаю, ты этого хочешь?

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)  

SQL Fiddle: Проверьте здесь

НЕ mySQL, но для других людей, которые находят этот вопрос и используют SQL, это еще один способ разрешить пост-тег наибольшее число на группу проблема связана с использованием _ 1_ в MS SQL

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

Вот пример в SqlFiddle

Поскольку это самый популярный вопрос в отношении этой проблемы, я также повторно отправлю на него еще один ответ:

Похоже, есть более простой способ сделать это (но только в MySQL):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Укажите ответ пользователя Bohemian в этот вопрос за такой краткий и элегантный ответ на эту проблему.

Изменить: хотя это решение работает для многих людей, оно может быть нестабильным в долгосрочной перспективе, поскольку MySQL не гарантирует, что оператор GROUP BY вернет значимые значения для столбцов, не входящих в список GROUP BY. Так что используйте это решение на свой страх и риск!

Я бы использовал это:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

Подзапрос SELECT, возможно, не слишком эффективен, но в предложении JOIN его можно использовать. Я не эксперт в оптимизации запросов, но я пробовал использовать MySQL, PostgreSQL, FireBird, и он действительно работает очень хорошо.

Вы можете использовать эту схему в нескольких соединениях и с предложением WHERE. Это мой рабочий пример (решение идентичной вашей задачи с таблицей "firmy"):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

Он запрашивается на таблицах, содержащих несколько десятков записей, и занимает менее 0,01 секунды на действительно не слишком мощной машине.

Я бы не стал использовать предложение IN (как уже упоминалось где-то выше). IN предназначен для использования с короткими списками констант, а не как фильтр запроса, построенный на подзапросе. Это связано с тем, что подзапрос в IN выполняется для каждой отсканированной записи, что может занять очень много времени.

Если у вас много полей в операторе select и вы хотите получить последнее значение для всех этих полей с помощью оптимизированного кода:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 

Как насчет этого:

SELECT all_fields.*  
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs  
LEFT OUTER JOIN yourtable AS all_fields 
ON max_recs.id = all_fields.id

Это решение делает только один выбор из YourTable, поэтому работает быстрее. Работает только для MySQL и SQLite (для SQLite удалите DESC) согласно тесту на sqlfiddle.com. Возможно, его можно настроить для работы на других языках, с которыми я не знаком.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id

Вот хороший способ сделать это

Используйте следующий код:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

Мне нравится делать это, ранжируя записи по некоторому столбцу. В этом случае ранжируйте rev значения, сгруппированные по id. Те, у кого больше rev, будут иметь более низкий рейтинг. Таким образом, самый высокий rev будет иметь рейтинг 1.

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

Не уверен, что введение переменных замедляет работу. Но, по крайней мере, я не запрашиваю YOURTABLE дважды.

Поле оборотов отсортировано в обратном порядке, а затем сгруппировано по идентификатору, который дает первую строку каждой группировки, которая имеет наивысшее значение оборотов.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Протестировано на http://sqlfiddle.com/ со следующими данными

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

Это дало следующий результат в MySql 5.5 и 5.6.

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

вот еще одно решение, надеюсь, оно кому-то поможет

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev

Ни один из этих ответов не помог мне.

Это то, что у меня сработало.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

Вот еще одно решение для получения записей только с полем, которое имеет максимальное значение для этого поля. Это работает для SQL400, над которой я работаю. В этом примере записи с максимальным значением в поле FIELD5 будут извлечены с помощью следующего оператора SQL.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

Объяснение

Это не чистый SQL. Это будет использовать SQLAlchemy ORM.

Я пришел сюда в поисках помощи по SQLAlchemy, поэтому я дублирую ответ Адриана Карнейро с версией python / SQLAlchemy, в частности, с внешней частью соединения.

Этот запрос отвечает на вопрос:

«Можете ли вы вернуть мне записи в этой группе записей (на основе того же идентификатора), которые имеют наивысший номер версии».

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

Код

MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
    MyTable, 
    MyTableAlias, 
    onclause=and_(
        MyTable.id == MyTableAlias.id,
        MyTable.version_int < MyTableAlias.version_int
    ),
    isouter=True
    )
).filter(
    MyTableAlias.id  == None,
).all()

Протестировано в базе данных PostgreSQL.

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

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

Затем я присоединил эти максимальные значения (# temp1) ко всем возможным комбинациям идентификатора / содержимого. Делая это, я, естественно, отфильтровываю не максимальные комбинации id / content, и у меня остаются только максимальные значения оборотов для каждой.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

Вы можете сделать выбор без объединения, если объедините rev и id в одно значение maxRevId для MAX(), а затем разделите его обратно на исходные значения:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

Это особенно быстро, когда вместо одной таблицы используется сложное соединение. При традиционных подходах сложное соединение будет выполняться дважды.

Вышеупомянутая комбинация проста с битовыми функциями, когда rev и id равны INT UNSIGNED (32 бит), а комбинированное значение соответствует BIGINT UNSIGNED (64 бит). Когда id & rev больше 32-битных значений или состоят из нескольких столбцов, вам необходимо объединить значение, например, в двоичное значение с подходящим заполнением для MAX().