Obtener la fila que tiene el valor máximo para una columna

Veo que muchas personas usan subconsultas o características específicas del proveedor para hacer esto, pero a menudo hago este tipo de consulta sin subconsultas de la siguiente manera. Utiliza SQL estándar, por lo que debería funcionar en cualquier 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;

En otras palabras: obtener la fila de t1 donde no existe otra fila con la misma UserId y una Fecha mayor.

(Pongo el identificador "Date" en delimitadores porque es una palabra reservada SQL.)

En el caso if t1."Date" = t2."Date", aparece la duplicación. Normalmente las tablas tienen la clave auto_inc(seq), por ejemplo id. Para evitar la duplicación se puede utilizar lo siguiente:

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;

Comentario de @Farhan:

Aquí hay una explicación más detallada:

Una unión externa intenta unir t1 con t2. De forma predeterminada, se devuelven todos los resultados de t1, y si hay una coincidencia en t2, también se devuelve. Si no hay coincidencia en t2 para una fila dada de t1, entonces la consulta aún devuelve la fila de t1, y usa NULL como marcador de posición para todas las columnas de t2. Así es como outer joins trabaja en general.

El truco en esta consulta es diseñar la condición coincidente de la unión de tal manera que t2 debe coincidir con la misma userid, y a mayor date. La idea es que si existe una fila en t2 que tiene un mayor date, entonces la fila en t1 se compara con no puede ser el mayor date para eso userid. Pero si no hay coincidencia {es decir, si no existe ninguna fila en t2 con una mayor date que la fila en t1 know sabemos que la fila en t1 era la fila con el mayor date para el dado userid.

En esos casos (cuando no hay coincidencia), las columnas de t2 serán NULL even incluso las columnas especificadas en la condición join. Así que es por eso que usamos WHERE t2.UserId IS NULL, porque estamos buscando los casos donde no se encontró ninguna fila con un mayor date para el dado userid.

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

No conozco los nombres exactos de sus columnas, pero sería algo como esto:

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

Al no estar en el trabajo, no tengo Oracle a mano, pero me parece recordar que Oracle permite múltiples columnas para ser emparejado en una cláusula IN, que al menos debe evitar las opciones que utilizan una subconsulta correlacionada, que rara vez es una buena idea.

Algo como esto, tal vez (no puedo recordar si la lista de columnas debe estar entre paréntesis o no):

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

EDITAR: Lo probé de verdad:

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

Así que funciona, aunque algunas de las cosas nuevas mencionadas en otros lugares pueden ser más performante.

Sé que pediste Oracle, pero en SQL 2005 ahora usamos esto:

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

¿No sería una cláusula de CALIFICACIÓN a la vez más simple y mejor?

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

Para el contexto, en Teradata aquí una prueba de tamaño decente de esto se ejecuta en 17s con esta versión QUALIFY y en 23s con la 'inline view'/Aldridge solution #1.

No tengo Oracle para probarlo, pero la solución más eficiente es usar consultas analíticas. Debería verse algo como esto:

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

Sospecho que puede deshacerse de la consulta externa y poner distinto en el interior, pero no estoy seguro. Mientras tanto, sé que este funciona.

Si quieres aprender sobre consultas analíticas, te sugiero que leas http://www.orafaq.com/node/55 y http://www.akadia.com/services/ora_analytic_functions.html. Aquí está el breve resumen.

Bajo el capó las consultas analíticas ordenan todo el conjunto de datos, luego lo procesan secuencialmente. A medida que lo procesa particiona el conjunto de datos de acuerdo con ciertos criterios, y luego para cada fila mira alguna ventana (por defecto el primer valor en la partición a la fila actual - que por defecto es también el más eficiente) y puede calcular valores utilizando un número de funciones analíticas (la lista de las cuales es muy similar a las funciones de agregado).

En este caso esto es lo que hace la consulta interna. Todo el conjunto de datos se ordena por userId y luego Date DESC. Luego lo procesa en una sola pasada. Para cada fila devuelve el ID de usuario y la primera fecha vista para ese ID de usuario (ya que las fechas están ordenadas DESC, esa es la fecha máxima). Esto le da su respuesta con filas duplicadas. A continuación, los distintos squashes externos se duplican.

Este no es un ejemplo particularmente espectacular de consultas analíticas. Para una victoria mucho mayor considere tomar una tabla de recibos financieros y calculando para cada usuario y recibo, un total corriente de lo que pagaron. Las consultas analíticas resuelven eso de manera eficiente. Otras soluciones son menos eficientes. Por eso son parte del estándar SQL de 2003. (Desafortunadamente Postgres no los tiene todavía. Grrr...)

Con PostgreSQL 8.4 o posterior, puede usar esto:

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

En Oracle 12c+, puede usar Consultas Top n junto con la función analítica rank para lograr esto de manera muy concisa sin subconsultas:

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

Lo anterior devuelve todas las filas con max my_date por usuario.

Si solo desea una fila con fecha máxima, reemplace la rank con 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  

Solo tenía que escribir un ejemplo " en vivo "en el trabajo:)

Este soporta múltiples valores para userId en la misma fecha.

Columnas: ID de usuario, Valor, Fecha

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
)

Puede usar FIRST_VALUE en lugar de MAX y buscarlo en el plan explain. No tuve tiempo de jugar con él.

Por supuesto, si busca a través de tablas enormes, probablemente sea mejor si usa sugerencias COMPLETAS en su consulta.

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

Pienso algo como esto. (Perdóname por cualquier error de sintaxis; estoy acostumbrado a usar HQL en este punto!)

EDITAR: También malinterpretar la pregunta! Corregida la consulta...

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

I cosa que shuold hacer esta variante a la consulta anterior:

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

(T-SQL) Primero obtiene todos los usuarios y su maxdate. Únase a la tabla para encontrar los valores correspondientes para los usuarios en 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

La respuesta aquí es solo Oracle. Aquí hay una respuesta un poco más sofisticada en todo SQL:

¿Quién tiene el mejor resultado general de la tarea (suma máxima de puntos de la tarea)?

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)

Y un ejemplo más difícil, que necesita alguna explicación, para el que no tengo tiempo atm:

Dar el libro (ISBN y título) que es más popular en 2008, es decir, que se toma prestado más a menudo en 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 esto ayude (a cualquiera).. :)

Saludos, Guus

Asumiendo que la fecha es única para un userId dado, aquí hay algo 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 

Llego bastante tarde a la fiesta, pero el siguiente truco superará tanto a las subconsultas correlacionadas como a cualquier función de análisis, pero tiene una restricción: los valores deben convertirse en cadenas. Así que funciona para fechas, números y otras cadenas. El código no se ve bien, pero el perfil de ejecución es genial.

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

La razón por la que este código funciona tan bien es que solo necesita escanear la tabla una vez. No requiere ningún índice y lo más importante no es necesario ordenar la tabla, que la mayoría de las funciones de análisis lo hacen. Sin embargo, los índices le ayudarán si necesita filtrar el resultado para un solo id de usuario.

Uso ROW_NUMBER() para asignar una clasificación única en descendente Date para cada UserId, luego filtre a la primera fila para cada UserId (i. e., 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

En mi humilde opinión esto funciona. HTH

Creo que esto debería 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

Primero intento malinterpretar la pregunta, siguiendo la respuesta superior, aquí hay un ejemplo completo con resultados correctos:

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)

Esto también se encargará de los duplicados (devuelve una fila por 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

Acabo de probar esto y parece funcionar en una tabla de registro

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

Esto debería ser tan simple como:

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

Si estás usando Postgres, puedes usar array_agg como

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

No estoy familiarizado con Oracle. Esto es lo que se me ocurrió

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 consultas devuelven los mismos resultados que la respuesta aceptada. Véase SQLFiddles:

1. Respuesta aceptada
2. Mi solución con Postgres
3. Mi solución con Oracle

Si (userId, Date) es único, es decir, ninguna fecha aparece dos veces para el mismo usuario entonces:

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;

Solución para MySQL que no tiene conceptos de partición 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

Referencia: http://benincampus.blogspot.com/2013/08/select-rows-which-have-maxmin-value-in.html