Menu
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?
689
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.
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.
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.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
