Cómo obtener el último registro por grupo en SQL
Me enfrento a un problema bastante interesante. Tengo una tabla con la siguiente estructura:
CREATE TABLE [dbo].[Event]
(
Id int IDENTITY(1,1) NOT NULL,
ApplicationId nvarchar(32) NOT NULL,
Name nvarchar(128) NOT NULL,
Description nvarchar(256) NULL,
Date nvarchar(16) NOT NULL,
Time nvarchar(16) NOT NULL,
EventType nvarchar(16) NOT NULL,
CONSTRAINT Event_PK PRIMARY KEY CLUSTERED ( Id ) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
)
Así que el problema es que tengo que mostrar estos datos en una cuadrícula. Hay dos requisitos. La primera es mostrar todos los eventos independientemente de qué aplicación los lanzó. Esto es simple: una instrucción select hará el trabajo muy fácilmente.
El segundo requisito es poder agrupar eventos por Application
. En otras palabras, mostrar todos los eventos de una manera que si el ApplicationId
se repite más de una vez, agarra solo la última entrada para cada aplicación. La clave principal del evento (Id) en este punto ya no es necesaria en esta consulta/vista.
También puede notar que la Fecha y la hora del evento están en formato de cadena. Esto está bien porque siguen los formatos de fecha y hora estándar: mm / dd / aaaa y hh:mm:ss. Puedo tirar de ellos de la siguiente manera:
Convert( DateTime, (Date + ' ' + Time)) AS 'TimeStamp'
Mi problema es que si utilizo funciones AGREGADAS en el resto de las columnas, no se cómo lo harían compórtate:
SELECT
ApplicationId,
MAX(Name),
MAX(Description),
MAX( CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp',
MAX( EventType )
FROM
Event
GROUP BY
ApplicationId
La razón por la que dudo en hacerlo es porque una función como MAX
devolverá el valor más grande para una columna dada de un (sub)conjunto de registros. No es necesario tirar del último registro!
¿Alguna idea sobre cómo seleccionar solo el último registro por solicitud?
8 answers
Puede usar una función de clasificación y una expresión de tabla común .
WITH e AS
(
SELECT *,
ROW_NUMBER() OVER
(
PARTITION BY ApplicationId
ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC
) AS Recency
FROM [Event]
)
SELECT *
FROM e
WHERE Recency = 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-06-01 13:15:45
Desde SQL Server 2012 puede simplemente
SELECT
[Month]
, [First] = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month])
, [Last] = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month] DESC)
FROM
[dbo].[Table]
GROUP BY [Month]
ORDER BY [Month]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-02-18 04:10:49
SELECT
E.ApplicationId,
E.Name,
E.Description,
CONVERT(DateTime, (E.Date + ' ' + E.Time)) AS 'TimeStamp',
E.EventType
FROM
Event E
JOIN (SELECT ApplicationId,
MAX(CONVERT(DateTime, (Date + ' ' + Time))) AS max_date
FROM Event
GROUP BY ApplicationId) EM
on EM.ApplicationId = E.ApplicationId
and EM.max_date = CONVERT(DateTime, (E.Date + ' ' + E.Time)))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-06-01 12:43:58
Puede usar una tabla subqery o CTE para hacer esto:
;WITH CTE_LatestEvents as (
SELECT
ApplicationId,
MAX( CONVERT(DateTime, (Date + ' ' + Time))) AS 'LatestTimeStamp',
FROM
Event
GROUP BY
ApplicationId
)
SELECT
ApplicationId,
Name,
Description,
CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp',
EventType
FROM
Event e
Join CTE_LatestEvents le
on e.applicationid = le.applicationid
and CONVERT(DateTime, (e.Date + ' ' + e.Time))) = le.LatestTimeStamp
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-06-01 12:45:23
Debido a que no tiene una cláusula where allí, el subconjunto de registros, son todos los registros. Pero creo que estás poniendo a Max en la(s) columna (s) equivocada (s). Esta consulta le dará lo que está buscando.
Select max(applicationid), name, description, CONVERT(DateTime, (Date + ' ' + Time))
from event
group by name, description, CONVERT(DateTime, (Date + ' ' + Time))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-06-01 12:48:31
Puede usar una sub consulta con group by - el argumento group by no necesita estar en select. Esto supone que Id es un auto incrementado de modo que el más grande es el más reciente.
SELECT
ApplicationId,
Name,
Description,
CONVERT(DateTime, (Date + ' ' + Time)) AS 'TimeStamp',
EventType
FROM
Event e
WHERE
Id in (select max(Id) from Event GROUP BY ApplicationId)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-06-01 12:51:56
Creo que funcionará para muchos que están dispuestos a buscar el último registro insertado y debería agruparse por:
Select * from (select * from TableName ORDER BY id DESC) AS x GROUP BY FieldName
Funcionará para lo siguiente:
Estructura del cuadro ID Nombre Estado 1 Junaid Sí 2 Jawad No 3 Fahad Sí 4 Junaid No 5 Kashif Yes
Resultados Después De La Consulta Anterior ID Nombre Estado 4 Junaid No 2 Jawad No 3 Fahad Sí 4 Kashif Yes
Simplemente resulta el último registro del grupo por nombres.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-05-07 07:45:32
Después de 6 años otra respuesta para SQL Server:
select t1.[Id], t2.[Value]
from [dbo].[Table] t1
outer apply (
select top 1 [Value]
from [dbo].[Table] t2
where t2.[Month]=t1.[Month]
order by [dbo].[Date] desc
)
Aunque me gusta mucho mejor la solución Postgresql con su característica distinct on que es más agradable de escribir y mucho más eficiente:
select distinct on (id),val
from tbl
order by id,val
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-07-25 06:30:54