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

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

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

Другими словами: получаем строки из t1, где не существует другой строки с тем же UserId и более поздней датой.

(Я обернул идентификатор "Date" в кавычки, потому что это зарезервированное слово SQL.)

В случае, если t1."Date" = t2."Date", может происходить дублирование. Обычно в таблицах есть ключ с автоинкрементом, например, id. Чтобы избежать дублирования, можно использовать следующий подход:

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date") 
         OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

Что касается комментария от @Farhan:

Вот более подробное объяснение:

Внешнее соединение пытается объединить t1 с t2. По умолчанию возвращаются все результаты из t1, и если существует совпадение в t2, оно также возвращается. Если для заданной строки из t1 нет совпадения в t2, запрос все равно возвращает строку из t1, и используется NULL в качестве плейсхолдера для всех столбцов из t2. Так работают внешние соединения в общем случае.

Хитрость этого запроса заключается в том, чтобы задать условие совпадения соединения так, чтобы t2 должно совпадать с тем же UserId, и иметь более позднюю дату. Идея в том, что если в t2 существует строка с более поздней датой, то строка в t1, с которой она сравнивается, не может быть самой поздней датой для этого UserId. Но если совпадения нет, т.е. если в t2 нет строки с более поздней датой, чем строка в t1, мы знаем, что строка в t1 имеет самую позднюю дату для данного UserId.

В этих случаях (когда совпадений нет) столбцы t2 будут равны NULL — даже столбцы, указанные в условии соединения. Поэтому мы используем условие WHERE t2.UserId IS NULL, потому что мы ищем случаи, когда не была найдена строка с более поздней датой для данного UserId.

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

select userid,
       my_date,
       ...
from
(
select userid,
       my_date,
       ...
       max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

"Аналитические функции крутят!"

Правка: В ответ на первый комментарий...

"Использование аналитических запросов и самообъединение противоречит смыслу аналитических запросов."

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

"По умолчанию окно в Oracle идет от первой строки в партиции до текущей."

Клаузула окна применима только при наличии клаузулы order by. Без нее клауза окон не применяется по умолчанию, и ее нельзя явно указать.

Код работает.

Этот SQL-запрос выбирает userid и максимальное значение value, соответствующее самой последней дате для каждого пользователя из таблицы. Давайте рассмотрим его подробнее:

• SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC): здесь мы выбираем userid и максимальное значение value, используя конструкцию KEEP. Она позволяет сохранить максимальное значение для первых строк, упорядоченных по date в порядке убывания. То есть, при наличии нескольких одинаковых максимальных значений value для одной даты, будет выбрано первое из них.

• FROM table: указывает, что данные выбираются из указанной таблицы.

• GROUP BY userid: эта часть группирует результаты по userid, так что для каждого уникального userid будет возвращён один результат с максимальным значением value для самой последней даты.

Таким образом, данный запрос возвращает userid и соответствующее максимальное значение value для каждой записи, основываясь на самой последней дате. Если у вас есть дополнительные вопросы или нужна помощь с другим SQL-запросом, не стесняйтесь спрашивать!

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

SELECT userid, value
FROM users u1
WHERE date = (
    SELECT MAX(date)
    FROM users u2
    WHERE u1.userid = u2.userid
)

Этот запрос выбирает userid и value из таблицы users, где дата совпадает с максимальной датой для соответствующего пользователя. Убедитесь, что вы подставили свои названия столбцов и таблиц.

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

Что-то вроде этого, возможно (не помню, нужно ли заключать список столбцов в круглые скобки):

SELECT * 
FROM MyTable
WHERE (User, Date) IN
  ( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

ИЗМЕНЕНИЕ: Я только что попробовал это на практике:

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
  2  where (usr, dt) in 
  3  ( select usr, max(dt) from mytable group by usr)
  4  /

U DT
- ---------
A 01-JAN-09
B 01-JAN-09

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