Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the last record per group in SQL

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?

like image 689
bleepzter Avatar asked Jun 01 '11 12:06

bleepzter


People also ask

How do I get last record by group by?

The group by will always return the first record in the group on the result set. SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id ); This will return the posts with the highest IDs in each group.

How do you group by and get the latest record in SQL?

Retrieving the last record in each group using GROUP BY There are two solutions explained here using the GROUP BY clause. In both these solutions, we will be using the MAX() function to get the maximum value of id and then retrieving the other columns corresponding to this maximum id.

How do I select the last record in SQL?

To get the last record, the following is the query. mysql> select *from getLastRecord ORDER BY id DESC LIMIT 1; The following is the output. The above output shows that we have fetched the last record, with Id 4 and Name Carol.


1 Answers

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 
like image 168
Anthony Faull Avatar answered Sep 21 '22 06:09

Anthony Faull