How to get the last record per group in SQL

Question

I am facing a rather interesting problem. I have a table with the following structure:

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

So the issue is that I have to display this data in a grid. There are two requirements. The first one is to display all events regardless of what application threw them. This is simple - a select statement will do the job very easily.

The second requirement is to be able to group events by Application. In other words display all events in a way that if the ApplicationId is repeated more than once, grab only the last entry for every application. The primary key of the Event (Id) at this point is no longer needed in this query/view.

You may also notice that the Event Date and Time are in string format. This is ok because they follow the standard date time formats: mm/dd/yyyy and hh:mm:ss. I can pull those as follows:

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

My issue is that if I use AGGREGATE functions on the rest of the columns I don't know how would they behave:

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

The reason I am hesitant to do so is because a function such as MAX will return the largest value for a given column from a (sub)set of records. It does not necessary pull the last record!

Any ideas on how to select only the last record on a per application basis?

Answer 1

You can use a ranking function and a common table expression.

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

Answer 2

Since SQL Server 2012 you can simply

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]

Answer 3

You can use a sub query with group by - the group by argument does not need to be in the select. This assumes Id is a auto incremented so that the largest one is the most recent.

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)

Answer 4

I think it will work for many out there willing to fetch the last inserted record and it should be group by:

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

It will work for the following:

Table Structure ID Name Status 1 Junaid Yes 2 Jawad No 3 Fahad Yes 4 Junaid No 5 Kashif Yes

Results After Query Above ID Name Status 4 Junaid No 2 Jawad No 3 Fahad Yes 4 Kashif Yes

It is simply resulting the last record of group by names.

Answer 5

After 6 years another answer for 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  
  )  

Although I like Postgresql solution much better with its distinct on feature which is nicer to type and much more efficient:

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

Answer 6

At first I used to use CTE with row_number, but one example in SQL server certification course showed me better example (judging by getting consistently better execution plans):

SELECT
  ApplicationId,
  Name,
  Description,
  CONVERT(DateTime, (Date + ' ' + Time)) AS 'TimeStamp',
  EventType
FROM
  Event AS E
WHERE
  NOT EXISTS(SELECT * FROM Event AS Newer WHERE Newer.ApplicationId = E.ApplicationId AND Newer.Id > E.Id)
GROUP BY
  ApplicationId

I'm assuming that larger Id implies larger Date+Time (otherwise I would use convert to datetime, which is not SARGable though). This query will find youngest record - for which younger record doesn't exist. If indexes are set right, this will use index seeks. The alternative with ranking function generally uses table scan, since it ranks all records.

Answer 7

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

Answer 8

Because you don't have a where clause in there, the subset of records, is all the records. But you are putting max on the wrong column(s) I think. This query will give you what you're looking for.

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

Answer 9

You can use a subqery or CTE table to do this:

;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

Answer 10

I had the same issue. Now, I didn't want to overly complicate things with CTEs and "OVER". Here's a simple example. I wrote a sub-query with a group by MAX(DateEntered). You can possibly want to do by ID if it's int, for instance, that'd be more accurate than Date/Time. In any case, once you have this sub-query, you just inner-join that to your main query to act as a filter for the records. It's that simple.

Table a is my users table. Table b is the sub-query and table c is the table I want "filtered".

SELECT DISTINCT a.FirstName,a.LastName,a.ImagePath, c.MessageText
        FROM [AuthUsers] a 
            INNER JOIN (SELECT MessageFromId,MAX(DateEntered) AS LastEntered FROM ChatRoomConversation GROUP BY MessageFrom) AS b
                ON a.Id=b.MessageFromId
            INNER JOIN ChatRoomConversation c
                ON b.LastEntered=c.DateEntered