Obtener 1 fila superior de cada grupo

Acabo de aprender a usar cross apply. He aquí cómo usarlo en este escenario:

 select d.DocumentID, ds.Status, ds.DateCreated 
 from Documents as d 
 cross apply 
     (select top 1 Status, DateCreated
      from DocumentStatusLogs 
      where DocumentID = d.DocumentId
      order by DateCreated desc) as ds

He hecho algunos tiempos sobre las diversas recomendaciones aquí, y los resultados realmente dependen del tamaño de la tabla involucrada, pero la solución más consistente es usar la APLICACIÓN CRUZADA Estas pruebas se ejecutaron contra SQL Server 2008-R2, utilizando una tabla con 6.500 registros, y otra (esquema idéntico) con 137 millones de registros. Las columnas que se consultan son parte de la clave primaria en la tabla, y el ancho de la tabla es muy pequeño (alrededor de 30 bytes). Los tiempos son reportados por SQL Server desde el plan de ejecución real.

Query                                  Time for 6500 (ms)    Time for 137M(ms)

CROSS APPLY                                    17.9                17.9
SELECT WHERE col = (SELECT MAX(COL)…)           6.6               854.4
DENSE_RANK() OVER PARTITION                     6.6               907.1

Creo que lo realmente sorprendente fue lo consistente que era el tiempo para la APLICACIÓN CRUZADA, independientemente del número de filas involucradas.

SELECT * FROM
DocumentStatusLogs JOIN (
  SELECT DocumentID, MAX(DateCreated) DateCreated
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ) max_date USING (DocumentID, DateCreated)

¿Qué servidor de base de datos? Este código no funciona en todos ellos.

Con respecto a la segunda mitad de su pregunta, me parece razonable incluir el estado como una columna. Puede dejar DocumentStatusLogs como un registro, pero aún así almacenar la información más reciente en la tabla principal.

POR cierto, si ya tiene la columna DateCreated en la tabla Documentos, puede unirse a DocumentStatusLogs usando eso (siempre y cuando DateCreated sea único en DocumentStatusLogs).

Editar: MsSQL no soporta el USO, así que cámbialo a:

ON DocumentStatusLogs.DocumentID = max_date.DocumentID AND DocumentStatusLogs.DateCreated = max_date.DateCreated

Si te preocupa el rendimiento, también puedes hacer esto con MAX ():

SELECT *
FROM DocumentStatusLogs D
WHERE DateCreated = (SELECT MAX(DateCreated) FROM DocumentStatusLogs WHERE ID = D.ID)

ROW_NUMBER() requiere una especie de todas las filas de su instrucción SELECT, mientras que MAX no. Debería acelerar drásticamente su consulta.

Este es un hilo bastante viejo, pero pensé que iba a tirar mis dos centavos en el mismo, ya que la respuesta aceptada no funcionó particularmente bien para mí. Probé la solución de gbn en un conjunto de datos grande y encontré que era terriblemente lento (>45 segundos en más de 5 millones de registros en SQL Server 2012). Mirando el plan de ejecución es obvio que el problema es que requiere una operación de ORDENACIÓN que ralentiza las cosas significativamente.

Aquí hay una alternativa que saqué de entity framework eso no necesita ninguna operación de ORDENACIÓN y hace una búsqueda de índices NO agrupados. Esto reduce el tiempo de ejecución a

SELECT 
[Limit1].[DocumentID] AS [DocumentID], 
[Limit1].[Status] AS [Status], 
[Limit1].[DateCreated] AS [DateCreated]
FROM   (SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM [dbo].[DocumentStatusLogs] AS [Extent1]) AS [Distinct1]
OUTER APPLY  (SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
    FROM (SELECT 
        [Extent2].[ID] AS [ID], 
        [Extent2].[DocumentID] AS [DocumentID], 
        [Extent2].[Status] AS [Status], 
        [Extent2].[DateCreated] AS [DateCreated]
        FROM [dbo].[DocumentStatusLogs] AS [Extent2]
        WHERE ([Distinct1].[DocumentID] = [Extent2].[DocumentID])
    )  AS [Project2]
    ORDER BY [Project2].[ID] DESC) AS [Limit1]

Ahora asumo algo que no está completamente especificado en la pregunta original, pero si el diseño de la tabla es tal que su columna de ID es un ID de incremento automático, y el DateCreated se establece en la fecha actual con cada inserción, entonces incluso sin ejecutar con mi consulta anterior, en realidad podría obtener un aumento la solución de gbn (aproximadamente la mitad del tiempo de ejecución) solo desde ordenando en ID en lugar de ordenar en DateCreated ya que esto proporcionará un orden idéntico y es un orden más rápido.

Sé que este es un hilo viejo, pero las soluciones TOP 1 WITH TIES es bastante agradable y podría ser útil para alguna lectura a través de las soluciones.

select top 1 with ties
   DocumentID
  ,Status
  ,DateCreated
from DocumentStatusLogs
order by row_number() over (partition by DocumentID order by DateCreated desc)

Puede encontrar más información sobre la cláusula TOP aquí.

Mi código para seleccionar el top 1 de cada grupo

select a.* from #DocumentStatusLogs a where 
 datecreated in( select top 1 datecreated from #DocumentStatusLogs b
where 
a.documentid = b.documentid
order by datecreated desc
)

Esta es una de las preguntas más fáciles de encontrar sobre el tema, por lo que quería dar una respuesta moderna a la ti (tanto para mi referencia como para ayudar a otros). Usando over y first value puedes hacer un trabajo corto de la consulta anterior:

select distinct DocumentID
  , first_value(status) over (partition by DocumentID order by DateCreated Desc) as Status
  , first_value(DateCreated) over (partition by DocumentID order by DateCreated Desc) as DateCreated
From DocumentStatusLogs

Esto debería funcionar en sql server 2008 y posteriores. El primer valor se puede considerar como una forma de lograr select top 1 cuando se usa una cláusula over. Over permite agrupar en la lista de selección, por lo que en lugar de escribir subconsultas anidadas (como muchas de las respuestas do), esto lo hace de una manera más legible. Espero que esto ayude.

Verificando la impresionante y correcta respuesta de Clint desde arriba:

El rendimiento entre las dos consultas a continuación es interesante. el 52% es el primero. Y el 48% es el segundo. Una mejora del 4% en el rendimiento utilizando DISTINTO en lugar de ORDEN POR. Pero ORDER BY tiene la ventaja de ordenar por múltiples columnas.

IF (OBJECT_ID('tempdb..#DocumentStatusLogs') IS NOT NULL) BEGIN DROP TABLE #DocumentStatusLogs END

CREATE TABLE #DocumentStatusLogs (
    [ID] int NOT NULL,
    [DocumentID] int NOT NULL,
    [Status] varchar(20),
    [DateCreated] datetime
)

INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (2, 1, 'S1', '7/29/2011 1:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (3, 1, 'S2', '7/30/2011 2:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 1, 'S1', '8/02/2011 3:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (1, 2, 'S1', '7/28/2011 4:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (4, 2, 'S2', '7/30/2011 5:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (5, 2, 'S3', '8/01/2011 6:00:00')
INSERT INTO #DocumentStatusLogs([ID], [DocumentID], [Status], [DateCreated]) VALUES (6, 3, 'S1', '8/02/2011 7:00:00')

Opción 1:

    SELECT
    [Extent1].[ID], 
    [Extent1].[DocumentID],
    [Extent1].[Status], 
    [Extent1].[DateCreated]
FROM #DocumentStatusLogs AS [Extent1]
    OUTER APPLY (
        SELECT TOP 1
            [Extent2].[ID], 
            [Extent2].[DocumentID],
            [Extent2].[Status], 
            [Extent2].[DateCreated]
        FROM #DocumentStatusLogs AS [Extent2]
        WHERE [Extent1].[DocumentID] = [Extent2].[DocumentID]
        ORDER BY [Extent2].[DateCreated] DESC, [Extent2].[ID] DESC
    ) AS [Project2]
WHERE ([Project2].[ID] IS NULL OR [Project2].[ID] = [Extent1].[ID])

Opción 2:

SELECT 
    [Limit1].[DocumentID] AS [ID], 
    [Limit1].[DocumentID] AS [DocumentID], 
    [Limit1].[Status] AS [Status], 
    [Limit1].[DateCreated] AS [DateCreated]
FROM (
    SELECT DISTINCT [Extent1].[DocumentID] AS [DocumentID] FROM #DocumentStatusLogs AS [Extent1]
) AS [Distinct1]
    OUTER APPLY  (
        SELECT TOP (1) [Project2].[ID] AS [ID], [Project2].[DocumentID] AS [DocumentID], [Project2].[Status] AS [Status], [Project2].[DateCreated] AS [DateCreated]
        FROM (
            SELECT 
                [Extent2].[ID] AS [ID], 
                [Extent2].[DocumentID] AS [DocumentID], 
                [Extent2].[Status] AS [Status], 
                [Extent2].[DateCreated] AS [DateCreated]
            FROM #DocumentStatusLogs AS [Extent2]
            WHERE [Distinct1].[DocumentID] = [Extent2].[DocumentID]
        )  AS [Project2]
        ORDER BY [Project2].[ID] DESC
    ) AS [Limit1]

M Management ' s Management Studio: Después de resaltar y ejecutar el primer bloque, resalte tanto la opción 1 como la Opción 2, Haga clic derecho - > [Mostrar Plan de ejecución Estimado]. A continuación, ejecutar toda la cosa para ver los resultados.

Resultados de la opción 1:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

Resultados de la opción 2:

ID  DocumentID  Status  DateCreated
6   1   S1  8/2/11 3:00
5   2   S3  8/1/11 6:00
6   3   S1  8/2/11 7:00

Nota:

Tiendo a usar APLICAR cuando quiero que un join sea 1-a-(1 de muchos).

Uso un JOIN si quiero que el join sea 1-to-many, o muchos-to-many.

Evito CTE con ROW_NUMBER () a menos que necesite hacer algo avanzado y esté bien con el rendimiento de ventana pena.

También evito EXISTS / IN subqueries en la cláusula WHERE o ON, ya que he experimentado esto causando algunos planes de ejecución terribles. Pero el kilometraje varía. ¡Revise el plan de ejecución y el rendimiento del perfil donde y cuando sea necesario!

En escenarios en los que desea evitar el uso de row_count(), también puede usar una combinación izquierda:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
left join DocumentStatusLogs filter 
    ON ds.DocumentID = filter.DocumentID
    -- Match any row that has another row that was created after it.
    AND ds.DateCreated < filter.DateCreated
-- then filter out any rows that matched 
where filter.DocumentID is null 

Para el esquema de ejemplo, también podría usar un "no en subconsulta", que generalmente compila la misma salida que la combinación izquierda:

select ds.DocumentID, ds.Status, ds.DateCreated 
from DocumentStatusLogs ds
WHERE ds.ID NOT IN (
    SELECT filter.ID 
    FROM DocumentStatusLogs filter
    WHERE ds.DocumentID = filter.DocumentID
        AND ds.DateCreated < filter.DateCreated)

Tenga en cuenta que el patrón de subconsulta no funcionaría si la tabla no tuviera al menos una clave/restricción/índice único de una sola columna, en este caso la clave primaria "Id".

Ambas consultas tienden a ser más "caras" que el row_count() consulta (medida por Query Analyzer). Sin embargo, es posible que encuentre escenarios en los que devuelvan resultados más rápido o habiliten otras optimizaciones.

Prueba esto:

        SELECT [DocumentID], 
        [tmpRez].value('/x[2]','varchar(20)') as [Status],
 [tmpRez].value('/x[3]','datetime') as [DateCreated] 
FROM (
        SELECT [DocumentID],
    cast('<x>'+max(cast([ID] as varchar(10))+'</x><x>'+[Status]+'</x><x>'
    +cast([DateCreated] as varchar(20)))+'</x>' as XML) as [tmpRez]
        FROM DocumentStatusLogs
        GROUP by DocumentID) as [tmpQry]

SELECT o.*
FROM `DocumentStatusLogs` o                   
  LEFT JOIN `DocumentStatusLogs` b                   
  ON o.DocumentID = b.DocumentID AND o.DateCreated < b.DateCreated
 WHERE b.DocumentID is NULL ;

Si desea devolver solo el orden de documentos recientes por DateCreated, devolverá solo el documento top 1 por documentId

Este es el TSQL más vainilla que se me ocurre

    SELECT * FROM DocumentStatusLogs D1 JOIN
    (
      SELECT
        DocumentID,MAX(DateCreated) AS MaxDate
      FROM
        DocumentStatusLogs
      GROUP BY
        DocumentID
    ) D2
    ON
      D2.DocumentID=D1.DocumentID
    AND
      D2.MaxDate=D1.DateCreated

Está comprobado en SQLite que puede usar la siguiente consulta simple con GROUP BY

SELECT MAX(DateCreated), *
FROM DocumentStatusLogs
GROUP BY DocumentID

Aquí MAX ayudar a obtener el máximo DateCreated DE cada grupo.

Pero parece que MYSQL no asocia * - columns con el valor de max DateCreated : (