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

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 2

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 3

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 4

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 5

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 6

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