Come ottenere l’ultimo record per gruppo in SQL

Sto affrontando un problema piuttosto interessante. Ho una tabella con la seguente struttura:

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

Quindi il problema è che devo visualizzare questi dati in una griglia. Ci sono due requisiti. Il primo è quello di visualizzare tutti gli eventi indipendentemente da quale applicazione li ha lanciati. Questo è semplice – una dichiarazione selezionata farà il lavoro molto facilmente.

Il secondo requisito è di essere in grado di raggruppare gli eventi in base Application . In altre parole, visualizza tutti gli eventi in modo che se ApplicationId viene ripetuto più di una volta, prendi solo l’ultima voce per ogni applicazione. La chiave primaria dell’evento (Id) a questo punto non è più necessaria in questa query / vista.

Si può inoltre notare che la data e l’ora dell’evento sono in formato stringa. Questo va bene perché seguono i formati di data e ora standard: mm / gg / aaaa e hh: mm: ss. Posso tirare quelli come segue:

 Convert( DateTime, (Date + ' ' + Time)) AS 'TimeStamp' 

Il mio problema è che se utilizzo le funzioni AGGREGATE sul resto delle colonne non so come si comporterebbero:

 SELECT ApplicationId, MAX(Name), MAX(Description), MAX( CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp', MAX( EventType ) FROM Event GROUP BY ApplicationId 

Il motivo per cui sono titubante è perché una funzione come MAX restituirà il valore più grande per una data colonna da un (sotto) insieme di record. Non è necessario tirare l’ultimo record!

Qualche idea su come selezionare solo l’ultimo record in base all’applicazione?

È ansible utilizzare una funzione di classifica e un’espressione di tabella comune .

 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 

Da SQL Server 2012 puoi semplicemente

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

È ansible utilizzare una tabella sottoquery o CTE per fare ciò:

 ;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 

Perché non hai una clausola where in là, il sottoinsieme di record, sono tutti i record. Ma tu stai mettendo il massimo sulla colonna sbagliata credo. Questa query ti darà quello che stai cercando.

 Select max(applicationid), name, description, CONVERT(DateTime, (Date + ' ' + Time)) from event group by name, description, CONVERT(DateTime, (Date + ' ' + Time)) 

È ansible utilizzare una query secondaria con gruppo per: il gruppo per argomento non deve essere presente nella selezione. Ciò presuppone che l’ID è un incremento automatico in modo che il più grande sia il più recente.

 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) 

Penso che funzionerà per molti là fuori disposti a recuperare l’ultimo record inserito e dovrebbe essere raggruppato per:

selezionare * da (selezionare * da TableName ORDER BY id DESC) AS x GROUP BY FieldName

Funzionerà per quanto segue:

Tabella Struttura Nome ID Stato 1 Junaid Sì 2 Jawad No 3 Fahad Si 4 Junaid No 5 Kashif Sì

Risultati dopo la query sopra ID Nome Stato 4 Junaid No 2 Jawad No 3 Fahad Sì 4 Kashif Sì

È semplicemente risultante l’ultimo record di gruppo per nome.

Dopo 6 anni un’altra risposta per 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 ) 

Anche se mi piace la soluzione Postgresql molto meglio con la sua caratteristica distinta che è più carina da digitare e molto più efficiente:

 select distinct on (id),val from tbl order by id,val