Busque a linha que tem o valor Máximo de uma coluna

Mesa:

UserId, Value, Date. 

Eu quero obter o UserId, valor para o máximo (data) para cada UserId. Ou seja, o valor de cada UserId que possui a data mais recente. Existe uma maneira de fazer isso simplesmente em SQL? (Preferencialmente Oracle)

Atualização: Desculpas para qualquer ambiguidade: Eu preciso obter todos os UserIds. Mas para cada UserId, apenas essa linha em que esse usuário tem a data mais recente.

Isso recuperará todas as linhas para as quais o valor da coluna my_date é igual ao valor máximo de my_date para esse ID do usuário. Isso pode recuperar várias linhas para o ID do usuário em que a data máxima está em várias linhas.

 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 

“Funções analíticas rock”

Edit: Com relação ao primeiro comentário …

“usando consultas analíticas e uma auto-adesão anula o propósito de consultas analíticas”

Não há auto-adesão neste código. Em vez disso, existe um predicado colocado no resultado da visão em linha que contém a function analítica – um assunto muito diferente e uma prática completamente padrão.

“A janela padrão no Oracle é da primeira linha da partição para a atual”

A cláusula de uso de janelas é aplicável somente na presença da cláusula order by. Sem cláusula order by, nenhuma cláusula de janelamento é aplicada por padrão e nenhuma pode ser explicitamente especificada.

O código funciona.

Vejo muitas pessoas usar subconsultas ou resources específicos de fornecedores para fazer isso, mas muitas vezes faço esse tipo de consulta sem subconsultas da seguinte maneira. Ele usa SQL simples e padrão, por isso deve funcionar em qualquer marca de RDBMS.

 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; 

Em outras palavras: busque a linha de t1 onde não existe outra linha com o mesmo UserId e uma Data maior.

(Eu coloco o identificador "Date" nos delimitadores porque é uma palavra reservada do SQL.)

No caso de se t1."Date" = t2."Date" , aparece o dobro. Normalmente, as tabelas possuem a chave auto_inc(seq) , por exemplo, id . Para evitar a duplicação pode ser usado a seguir:

 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; 

Re comentário de @Farhan:

Aqui está uma explicação mais detalhada:

Uma junit externa tenta unir t1 com t2. Por padrão, todos os resultados de t1 são retornados e, se houver uma correspondência em t2, ele também será retornado. Se não houver correspondência em t2 para uma determinada linha de t1, a consulta ainda retornará a linha de t1 e usará NULL como um espaço reservado para todas as colunas de t2. É assim que as junções externas funcionam em geral.

O truque nessa consulta é projetar a condição de correspondência da junit de modo que t2 deva corresponder ao mesmo ID do usuário e uma data maior . A ideia é que, se existir uma linha em t2 que tenha uma data maior, a linha em t1 em que é comparada não pode ser a maior data para esse userid. Mas se não houver correspondência - ou seja, se nenhuma linha existir em t2 com uma data maior que a linha em t1 - saberemos que a linha em t1 foi a linha com a maior data para o ID do usuário fornecido.

Nesses casos (quando não há correspondência), as colunas de t2 serão NULL - até mesmo as colunas especificadas na condição de associação. É por isso que usamos WHERE t2.UserId IS NULL , porque estamos procurando os casos em que nenhuma linha foi encontrada com uma data maior para o ID do usuário fornecido.

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

Eu não sei seus nomes exatos de colunas, mas seria algo assim:

     selecione userid, valor
       dos usuários u1
      where date = (selecione max (data)
                      dos usuários u2
                     onde u1.userid = u2.userid)

Não estando no trabalho, não tenho Oracle para entregar, mas me lembro que o Oracle permite que várias colunas sejam correspondidas em uma cláusula IN, o que deve pelo menos evitar as opções que usam uma subconsulta correlacionada, o que raramente é um bom idéia.

Algo como isto, talvez (não me lembro se a lista de colunas deve ser entre parênteses ou não):

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

EDIT: Apenas tentei de verdade:

 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 

Então funciona, embora algumas das coisas novas e mencionadas em outros lugares possam ter mais desempenho.

Eu sei que você pediu Oracle, mas no SQL 2005 agora usamos isso:

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

Uma cláusula QUALIFY não seria mais simples e melhor?

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

Para o contexto, no Teradata, um teste de tamanho decente é executado em 17s com essa versão QUALIFY e em 23s com a solução ‘inline view’ / Aldridge # 1.

Eu não tenho Oracle para testá-lo, mas a solução mais eficiente é usar consultas analíticas. Deve ser algo como isto:

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

Eu suspeito que você pode se livrar da consulta externa e colocar distinta no interior, mas não tenho certeza. Enquanto isso, sei que este funciona.

Se você quiser saber mais sobre consultas analíticas, sugiro ler http://www.orafaq.com/node/55 e http://www.akadia.com/services/ora_analytic_functions.html . Aqui está o breve resumo.

Nas consultas analíticas, o dataset inteiro é processado em sequência. Conforme você o processa, particiona o dataset de acordo com determinados critérios e, em seguida, para cada linha examina uma janela (o padrão é o primeiro valor da partição para a linha atual – esse padrão também é o mais eficiente) e pode calcular valores usando um número de funções analíticas (cuja lista é muito semelhante às funções agregadas).

Nesse caso, aqui está o que a consulta interna faz. O dataset inteiro é classificado por UserId e depois por Date DESC. Em seguida, ele processa em uma passagem. Para cada linha, você retorna o UserId e a primeira Data vista para esse UserId (já que as datas são classificadas como DESC, essa é a data máxima). Isso lhe dá sua resposta com linhas duplicadas. Então o DISTINCT exterior repete as duplicatas.

Este não é um exemplo particularmente espetacular de consultas analíticas. Para uma vitória muito maior, considere tomar uma tabela de recibos financeiros e calcular, para cada usuário e recibo, um total do que eles pagaram. Consultas analíticas resolvem isso com eficiência. Outras soluções são menos eficientes. É por isso que eles fazem parte do padrão SQL de 2003. (Infelizmente o Postgres ainda não os tem. Grrr …)

Com o PostgreSQL 8.4 ou posterior, você pode usar isto:

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

Apenas tive que escrever um exemplo “vivo” no trabalho 🙂

Este suporta vários valores para UserId na mesma data.

Colunas: UserId, Valor, Data

 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 <> GROUP BY UserId, Date ) 

Você pode usar FIRST_VALUE em vez de MAX e consultá-lo no plano de explicação. Eu não tive tempo para brincar com isso.

É claro que, se pesquisar em tabelas grandes, provavelmente será melhor se você usar as dicas FULL em sua consulta.

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

No Oracle 12c+ , você pode usar as principais n consultas junto com a rank analítica de funções para obter isso de maneira muito concisa, sem subconsultas:

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

O acima retorna todas as linhas com max my_date por usuário.

Se você quiser apenas uma linha com data máxima, substitua a rank por 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; 

Eu penso algo assim. (Perdoe-me por quaisquer erros de syntax; estou acostumado a usar o HQL neste ponto!)

EDIT: também interpretou mal a questão! Corrigida a consulta …

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

Eu acho que você deve fazer essa variante para consulta anterior:

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

(T-SQL) Primeiro, pegue todos os usuários e seu maxdate. Junte-se à tabela para encontrar os valores correspondentes para os usuários nos 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 

resultados:

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

A resposta aqui é apenas Oracle. Aqui está uma resposta um pouco mais sofisticada em todo o SQL:

Quem tem o melhor resultado geral de lição de casa (sum máxima de pontos de lição de casa)?

 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) 

E um exemplo mais difícil, que precisa de alguma explicação, para o qual não tenho tempo atm:

Dê o livro (ISBN e título) que é mais popular em 2008, ou seja, que é emprestado com mais frequência em 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); 

Espero que isso ajude (qualquer um) .. 🙂

Atenciosamente, Guus

Assumindo que Data é única para um determinado UserID, aqui está um pouco de 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 

Estou atrasado para a festa, mas o hack seguinte superará as subconsultas correlacionadas e qualquer function analítica, mas tem uma restrição: os valores devem ser convertidos em strings. Então funciona para datas, números e outras strings. O código não parece bom, mas o perfil de execução é ótimo.

 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 

A razão pela qual esse código funciona tão bem é que ele só precisa varrer a tabela uma vez. Ele não requer nenhum índice e, mais importante, não precisa classificar a tabela, o que a maioria das funções de análise faz. Os índices ajudarão, no entanto, se você precisar filtrar o resultado para um único ID do usuário.

 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 

IMHO isso funciona. HTH

Eu acho que isso deve funcionar?

 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 

Primeiro tentei interpretar mal a questão, seguindo a resposta principal, aqui está um exemplo completo com resultados corretos:

 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) 

Isso também cuidará das duplicatas (retorne uma linha para cada 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 

Apenas testei isso e parece funcionar em uma tabela de registro

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

Isso deve ser tão simples quanto:

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

Se você estiver usando o Postgres, você pode usar o array_agg como

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

Eu não estou familiarizado com o Oracle. Isto é o que eu inventei

 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 

Ambas as consultas retornam os mesmos resultados que a resposta aceita. Veja SQLFiddles:

  1. Resposta aceita
  2. Minha solução com o Postgres
  3. Minha solução com o Oracle

verifique este link se suas perguntas parecerem similares a essa página, então eu sugiro a seguinte consulta que dará a solução para esse link

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;

vai dado resultados precisos relacionados com esse link

Use ROW_NUMBER() para atribuir uma sorting única na Date decrescente de cada UserId , em seguida, filtre para a primeira linha de cada UserId (ou seja, 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; 

Se (UserID, Data) for exclusivo, ou seja, nenhuma data aparece duas vezes para o mesmo usuário, então:

 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;