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

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

Вы можете сделать это с помощью IN попробуйте это:

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

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

еще одним решением является использование коррелированного подзапроса:

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 (подзапрос, leftjoin), основанные на измерениях MySQL с таблицей InnoDB ~1million записей, размер группы: 1-3.

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

одна заключительная нота, поскольку leftjoin создает N*(n+1)/2 объединения в группы, его производительность может сильно зависеть от размера групп...

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

ХОРОШИЕ ВЕЩИ

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

подход

Это немного некрасиво и требует, чтобы вы знали что-то о диапазоне допустимых значения rev

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

Я думаю, что это самое простое решение :

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

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

Если вам понадобится только одна строка, это еще проще:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

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

• заказ по сотруднику.Зарплата DESC: заказать результаты по зарплате, с самыми высокими зарплатами в первую очередь.
• предел 1: возврат только одного результата.

понимая этот подход, решение любой из этих подобных проблем становится тривиальным: получить сотрудника с самой низкой зарплатой (изменить DESC на ASC), получить топ-10 зарабатывающих сотрудников (изменить лимит 1 на лимит 10), сортировать с помощью другого поля (изменить заказ по сотруднику.Зарплата на заказ по сотруднику.Комиссия) и др..

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

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

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

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

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

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

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

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

SELECT id, rev
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 и вы хотите получить последнее значение для всех этих полей с помощью оптимизированного кода:

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

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

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

выбор подзапроса не слишком эффективен, возможно, но в предложении 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 выполняется для каждой отсканированной записи, которая может сделать запрос, занимающий очень много времени.

не mySQL, но для других людей, которые находят этот вопрос и используют SQL, другой способ решить самый большой-n-на-группу проблема заключается в использовании Cross Apply в 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 и SQLite(для SQLite remove DESC) в соответствии с тестом on 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 два раза.

Если кто-то ищет LINQ verson, это, кажется, работает для меня:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

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

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

выбрать * от сотрудников где сотрудник.Зарплата в (выберите max (зарплата) из группы сотрудников по Employe_id) Заказ от сотрудника.Зарплата

вот еще одно решение для поиска записей только с полем, которое имеет максимальное значение для этого поля. Это работает для 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)

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

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

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

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

другое решение для раздела уже задокументировано в этом посте

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 Выберите хорошо работать на Oracle 10g.

вы можете сделать выбор без соединения при объединении 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 are INT UNSIGNED (32 бит) и комбинированное значение подходит для BIGINT UNSIGNED (64 бит). Когда id & rev больше, чем 32-разрядные значения или сделаны из нескольких столбцов, вам нужно объединить значение, например, двоичное значение с подходящим заполнением для MAX().

select * from yourtable
group by id
having rev=max(rev);

это работает для меня в sqlite3:

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

С помощью * вы получаете дубликат столбца rev, но это не большая проблема.

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;