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

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

Обновлено: Также неправильно прочитайте вопрос! Исправил запрос ...

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

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

    select userid, value
      from users u1
     where date = (select max(date)
                     from users u2
                    where u1.userid = u2.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

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

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. Без предложения order by по умолчанию не применяется оконное предложение, и его нельзя указать явно.

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

Если (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, Value FROM Users U1 WHERE 
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)

Предполагая, что Дата уникальна для данного UserID, вот некоторый 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,  
   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 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

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

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

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

Это должно быть так просто, как:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.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)

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

Я знаю, что вы просили 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

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

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

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

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

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

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

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

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

В случае t1."Date" = t2."Date" появляется удвоение. Обычно в таблицах есть ключ auto_inc(seq), например 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, оно также возвращается. Если в t2 нет соответствия для данной строки t1, запрос все равно возвращает строку t1 и использует NULL в качестве заполнителя для всех столбцов t2. Именно так в целом работают внешние соединения.

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

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

Не на работе, у меня нет 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

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

Это также позаботится о дубликатах (верните одну строку для каждого 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

Ответ здесь только 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)

И более сложный пример, требующий некоторых пояснений, на который у меня нет времени:

Укажите книгу (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);

Надеюсь, это поможет (кому угодно) .. :)

С уважением, Guus

Просто протестировал это, и, похоже, он работает в таблице регистрации

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

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

Он поддерживает несколько значений UserId на дату такой же.

Столбцы: UserId, значение, дата

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 и найти его в плане объяснения. У меня не было времени поиграть с этим.

Конечно, при поиске в огромных таблицах лучше использовать в запросе ПОЛНЫЕ подсказки.

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

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

Для контекста, здесь на Teradata тест приличного размера выполняется за 17 секунд с этой версией QUALIFY и за 23 секунды с «встроенным представлением» / решением Aldridge # 1.

В 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

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

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

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

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

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

Если вы используете 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

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

select distinct sno,item_name,max(start_date) over(partition by sno),max(end_date) over(partition by sno),max(creation_date) over(partition by sno), max(last_modified_date) over(partition by sno) from uniq_select_records order by sno,item_name asc;

даст точные результаты, связанные с этой ссылкой

В 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;

Вышеупомянутое возвращает все строки с максимальным значением 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 T.UserId,T.dt from (select UserId,max(dt) 
over (partition by UserId) as dt from t_users)T where T.dt=dt;

Это приведет к получению результатов независимо от повторяющихся значений UserId. Если ваш UserId уникален, все станет проще:

select UserId,max(dt) from t_users group by UserId;

SELECT a.* 
FROM user a INNER JOIN (SELECT userid,Max(date) AS date12 FROM user1 GROUP BY userid) b  
ON a.date=b.date12 AND a.userid=b.userid ORDER BY a.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 a.userid,a.values1,b.mm 
FROM table_name a,(SELECT userid,Max(date1)AS mm FROM table_name GROUP BY userid) b
WHERE a.userid=b.userid AND a.DATE1=b.mm;

Ниже запрос может работать:

SELECT user_id, value, date , row_number() OVER (PARTITION BY user_id ORDER BY date desc) AS rn
FROM table_name
WHERE rn= 1