Comment obtenir le dernier enregistrement par groupe dans SQL

Je suis confronté à un problème plutôt intéressant. J'ai une table avec la structure suivante:

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

Donc, le problème est que je dois afficher ces données dans une grille. Il y a deux exigences. Le premier est d'afficher tous les événements, quelle que soit l'application qui les a lancés. C'est simple - une instruction select fera le travail très facilement.

La deuxième exigence est de pouvoir regrouper les événements par Application. En d'autres termes, afficher tous les événements d'une manière que si le ApplicationId est répété plus d'une fois, prenez seulement la dernière entrée pour chaque application. La clé primaire de l'Événement (Id) à ce stade, n'est plus nécessaire à cette requête.

Vous pouvez également remarquer que la Date et L'Heure de l'événement sont au format de chaîne. C'est ok car ils suivent les formats de date et d'heure standard: MM / JJ/AAAA et hh: mm: SS. Je peux les tirer comme suit: 

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

Mon problème est que si j'utilise des fonctions D'agrégation sur le reste des colonnes, Je ne sais pas comment le feraient-elles se comporter: 

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

La raison pour laquelle j'hésite à le faire parce qu'une fonction telle que MAX qui retourne la plus grande valeur pour une colonne donnée à partir d'une (sous -) ensemble d'enregistrements. Il ne faut pas tirer le dernier enregistrement!

Des idées sur la façon de sélectionner uniquement le dernier enregistrement sur une base par application?

24
demandé sur Tab Alleman 2011-06-01 16:36:55

8 réponses

Vous pouvez utiliser une fonction de classement et une expression de table commune .

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
41
répondu Anthony Faull 2011-06-01 13:15:45

Depuis SQL Server 2012, vous pouvez simplement 

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]
6
répondu tartakynov 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)))
0
répondu manji 2011-06-01 12:43:58

Vous pouvez utiliser une sous-requête ou une table CTE pour ce faire: 

;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
0
répondu Jon Egerton 2011-06-01 12:45:23

Parce que vous n'avez pas de clause where, le sous-ensemble des enregistrements est tous les enregistrements. Mais vous mettez max sur la mauvaise colonne(s) je pense. Cette requête vous donnera ce que vous cherchez.

Select max(applicationid), name, description, CONVERT(DateTime, (Date + ' ' + Time)) 
from event
group by name, description, CONVERT(DateTime, (Date + ' ' + Time))
0
répondu Jody 2011-06-01 12:48:31

Vous pouvez utiliser une sous-requête avec le groupe par le groupe en argument n'a pas besoin d'être dans la sélection. Cela suppose que Id est une auto incrémentée de sorte que le plus grand est le plus récent.

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)
0
répondu cordsen 2011-06-01 12:51:56

Je pense que cela fonctionnera pour beaucoup de gens prêts à récupérer le dernier enregistrement inséré et il devrait être group by:

Select * from (select * from TableName ORDER BY id DESC) AS X GROUP BY FieldName

Cela fonctionnera pour ce qui suit:

Structure Du Tableau Nom de l'ID d'État 1 Junaid Oui 2 Jawad Non 3 Fahad Oui 4 Junaid Pas 5 Kashif Oui

Résultats Après La Requête Ci-Dessus Nom de l'ID d'État 4 Junaid Pas 2 Jawad Non 3 Fahad Oui 4 Kashif Oui

Il résulte simplement le dernier enregistrement de group by names.

0
répondu smjunaidiqbal 2013-05-07 07:45:32

Après 6 ans, une autre réponse pour 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  
  )

Bien que J'aime beaucoup mieux la solution Postgresql avec sa fonctionnalité distincte on qui est plus agréable à taper et beaucoup plus efficace: 

select distinct on (id),val  
from tbl  
order by id,val
0
répondu Domagoj Peharda 2017-07-25 06:30:54