Извлечь строку, которая имеет максимальное значение для столбца

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

(я помещаю идентификатор "дата" в разделители, потому что это зарезервированное слово SQL.)

In случай если t1."Date" = t2."Date", удвоение появляется. Обычно таблицы имеет

SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
  FROM table
  GROUP BY userid

Я не знаю ваши точные имена столбцов, но это будет что-то вроде этого:

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.userid)

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

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

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

EDIT: просто попробовал это по-настоящему:

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

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

Я знаю, что вы просили Oracle, но в SQL 2005 мы теперь используем это:

-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1

-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1

разве предложение QUALIFY не было бы самым простым и лучшим?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

для контекста, на Teradata здесь тест приличного размера этого выполняется в 17s с этой квалифицированной версией и в 23s с "встроенным представлением" /Aldridge solution #1.

У меня нет Oracle для тестирования, но наиболее эффективным решением является использование аналитических запросов. Это должно выглядеть примерно так:

SELECT DISTINCT
    UserId
  , MaxValue
FROM (
    SELECT UserId
      , FIRST (Value) Over (
          PARTITION BY UserId
          ORDER BY Date DESC
        ) MaxValue
    FROM SomeTable
  )

Я подозреваю, что вы можете избавиться от внешнего запроса и поставить distinct на внутренний, но я не уверен. Тем временем я знаю, что это работает.

Если вы хотите узнать об аналитических запросах, я бы предложил прочитать http://www.orafaq.com/node/55 и http://www.akadia.com/services/ora_analytic_functions.html. Вот краткий итог описания.

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

в этом случае вот что делает внутренний запрос. Весь набор данных сортируется по идентификатору пользователя, а затем по дате DESC. Затем он обрабатывает за один проход. Для каждой строки вы возвращаете идентификатор пользователя и первую дату, увиденную для этого идентификатора пользователя (поскольку даты сортируются DESC, это максимальная дата). Это дает вам ответ с дублированными строками. Затем внешние четкие раздавливания дублируются.

Это не a особенно эффектный пример аналитических запросов. Для гораздо большего выигрыша рассмотрите возможность взять таблицу финансовых поступлений и рассчитать для каждого пользователя и квитанции, общую сумму того, что они заплатили. Аналитические запросы решают это эффективно. Другие решения менее эффективны. Именно поэтому они являются частью стандарта SQL 2003. (К сожалению, у Postgres их еще нет. Гррр...)

С PostgreSQL 8.4 или более поздней версии, вы можете использовать это:

select user_id, user_value_1, user_value_2
  from (select user_id, user_value_1, user_value_2, row_number()
          over (partition by user_id order by user_date desc) 
        from users) as r
  where r.row_number=1

на Oracle 12c+, вы можете использовать Top n запросы вместе с аналитической функцией rank чтобы достичь этого очень кратко без подзапросы:

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

выше возвращает все строки с max my_date на пользователя.

если вы хотите только одну строку с максимальной датой, затем заменить rank С row_number:

select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties; 

Select  
   UserID,  
   Value,  
   Date  
From  
   Table,  
   (  
      Select  
          UserID,  
          Max(Date) as MDate  
      From  
          Table  
      Group by  
          UserID  
    ) as subQuery  
Where  
   Table.UserID = subQuery.UserID and  
   Table.Date = subQuery.mDate  

просто было написать "живой" пример на работе :)

Это один поддерживает несколько значений для идентификатора пользователя на то же самое дата.

столбцы: Идентификатор Пользователя, Значение, Дата

SELECT
   DISTINCT UserId,
   MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
   MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
   SELECT UserId, Date, SUM(Value) As Values
   FROM <<table_name>>
   GROUP BY UserId, Date
)

вы можете использовать FIRST_VALUE вместо MAX и посмотреть его в плане объяснения. У меня не было времени играть с ним.

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

select VALUE from TABLE1 where TIME = 
   (select max(TIME) from TABLE1 where DATE= 
   (select max(DATE) from TABLE1 where CRITERIA=CRITERIA))

Я думаю что-то вроде этого. (Простите меня за любые синтаксические ошибки; я привык использовать HQL в этот момент!)

EDIT: также неправильно понял вопрос! Исправлен запрос...

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
    SELECT MAX(Date)
    FROM Users AS maxtest
    WHERE maxtest.UserId = user.UserId
)

Я вещь, которую вы shuold сделать этот вариант к предыдущему запросу:

SELECT UserId, Value FROM Users U1 WHERE 
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)

(T-SQL) сначала получить всех пользователей и их maxdate. Соединитесь с таблицей, чтобы найти соответствующие значения для пользователей на maxdates.

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')

select T1.userid, T1.value, T1.date 
    from users T1,
    (select max(date) as maxdate, userid from users group by userid) T2    
    where T1.userid= T2.userid and T1.date = T2.maxdate

результаты:

userid      value       date                                    
----------- ----------- -------------------------- 
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000

ответ здесь только для Oracle. Вот немного более сложный ответ на всех SQL:

кто имеет лучший общий результат домашнего задания (максимальная сумма очков домашнего задания)?

SELECT FIRST, LAST, SUM(POINTS) AS TOTAL
FROM STUDENTS S, RESULTS R
WHERE S.SID = R.SID AND R.CAT = 'H'
GROUP BY S.SID, FIRST, LAST
HAVING SUM(POINTS) >= ALL (SELECT SUM (POINTS)
FROM RESULTS
WHERE CAT = 'H'
GROUP BY SID)

и более сложный пример, который нуждается в некотором объяснении, для которого у меня нет времени atm:

дайте книгу (ISBN и название), которая наиболее популярна в 2008 году, т. е. которая чаще всего заимствована в 2008 году.

SELECT X.ISBN, X.title, X.loans
FROM (SELECT Book.ISBN, Book.title, count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title) X
HAVING loans >= ALL (SELECT count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan 
ON Copy.copyId = Loan.copyId
GROUP BY Book.title);

надеюсь, что это поможет (кто-нибудь).. :)

С уважением, Гус

предполагая, что дата уникальна для данного идентификатора пользователя, вот некоторые TSQL:

SELECT 
    UserTest.UserID, UserTest.Value
FROM UserTest
INNER JOIN
(
    SELECT UserID, MAX(Date) MaxDate
    FROM UserTest
    GROUP BY UserID
) Dates
ON UserTest.UserID = Dates.UserID
AND UserTest.Date = Dates.MaxDate 

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

select
    userid,
    to_number(substr(max(to_char(date,'yyyymmdd') || to_char(value)), 9)) as value,
    max(date) as date
from 
    users
group by
    userid

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

использовать ROW_NUMBER() присвоить уникальный рейтинг по убыванию Date для каждого UserId, затем фильтр в первую строку для каждого UserId (т. е. ROW_NUMBER = 1).

SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
      FROM users) u
WHERE rn = 1;

select userid, value, date
  from thetable t1 ,
       ( select t2.userid, max(t2.date) date2 
           from thetable t2 
          group by t2.userid ) t3
 where t3.userid t1.userid and
       t3.date2 = t1.date

ИМХО это работает. ХТ

Я думаю, что это должно работать?

Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As 'Value'
From
Table T1
Group By
T1.UserId
Order By
T1.UserId

первая попытка я неправильно понял вопрос, после верхнего ответа, вот полный пример с правильными результатами:

CREATE TABLE table_name (id int, the_value varchar(2), the_date datetime);

INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'a','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'b','2/2/2002');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'c','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'d','3/3/2003');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'e','3/3/2003');

--

  select id, the_value
      from table_name u1
      where the_date = (select max(the_date)
                     from table_name u2
                     where u1.id = u2.id)

--

id          the_value
----------- ---------
2           d
2           e
1           b

(3 row(s) affected)

Это также позаботится о дубликатах (возвращает одну строку для каждого user_id):

SELECT *
FROM (
  SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
  FROM users u
) u2
WHERE u2.rowid = u2.last_rowid

просто проверил это, и это, кажется, работает на журнальной таблице

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc

Это должно быть так:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)

Если вы используете Postgres, вы можете использовать array_agg как

SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid

Я не знаком с Oracle. Вот что я придумал

SELECT 
  userid,
  MAX(adate),
  SUBSTR(
    (LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)),
    0,
    INSTR((LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)), ',')-1
  ) as value 
FROM YOURTABLE
GROUP BY userid 

оба запроса возвращают те же результаты, что и принятый ответ. См. Раздел SQLFiddles:

1. принято отвечать
2. мое решение с Postgres
3. мое решение с Oracle

Если (UserID, Date) является уникальным, т. е. ни одна дата не появляется дважды для одного и того же пользователя, то:

select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
                          from TheTable
                          group by UserID) UserMaxDate
     on TheTable.UserID = UserMaxDate.UserID
        TheTable.[Date] = UserMaxDate.MaxDate;

select   UserId,max(Date) over (partition by UserId) value from users;

решение для MySQL, которое не имеет понятия раздела KEEP, DENSE_RANK.

select userid,
       my_date,
       ...
from
(
select @sno:= case when @pid<>userid then 0
                    else @sno+1
    end as serialnumber, 
    @pid:=userid,
       my_Date,
       ...
from   users order by userid, my_date
) a
where a.serialnumber=0

Ссылка:http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html