July 30, 2015 at 3:45 am
Hi,
I want to get the latest comment for each id based on the maximum createdDate and not sure how to do this, tried to do Max but it was still returning all the rows.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Audit](
[id] [nvarchar](64) NOT NULL,
[createdDate] [datetime] NOT NULL,
[comment] [nvarchar](max) NULL,
[sequence] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into [Audit] values ('A1','2015-07-26 09:47:09.900','Still working on this',1)
insert into [Audit] values ('A1','2015-07-27 09:47:09.900','Be right back',2)
insert into [Audit] values ('A1','2015-07-28 09:47:09.900','No Feed back',3)
insert into [Audit] values ('A1','2015-07-29 09:47:09.900','No one knows',4)
insert into [Audit] values ('A2','2015-07-28 09:47:09.900','Keeping Working',5)
insert into [Audit] values ('A2','2015-07-29 09:47:09.900','Finshed',6)
insert into [Audit] values ('A3','2015-07-30 09:47:09.900','Completed',7)
insert into [Audit] values ('A3','2015-07-25 08:47:09.900','Succeeded',8)
insert into [Audit] values ('A3','2015-07-30 09:47:09.900','Succeeded',9)
insert into [Audit] values ('A4','2015-07-01 09:47:09.900','SQL Server relased',10)
insert into [Audit] values ('A4','2015-07-17 09:47:09.900','Failed Released',11)
insert into [Audit] values ('A4','2015-07-17 10:47:09.900','Still working on this',12)
Thanks
July 30, 2015 at 4:28 am
Here's an article[/url] I wrote on how to deal with versioned data. One of these solutions should work well for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2015 at 1:02 pm
SQL_Kills (7/30/2015)
Hi,I want to get the latest comment for each id based on the maximum createdDate and not sure how to do this, tried to do Max but it was still returning all the rows.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Audit](
[id] [nvarchar](64) NOT NULL,
[createdDate] [datetime] NOT NULL,
[comment] [nvarchar](max) NULL,
[sequence] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
insert into [Audit] values ('A1','2015-07-26 09:47:09.900','Still working on this',1)
insert into [Audit] values ('A1','2015-07-27 09:47:09.900','Be right back',2)
insert into [Audit] values ('A1','2015-07-28 09:47:09.900','No Feed back',3)
insert into [Audit] values ('A1','2015-07-29 09:47:09.900','No one knows',4)
insert into [Audit] values ('A2','2015-07-28 09:47:09.900','Keeping Working',5)
insert into [Audit] values ('A2','2015-07-29 09:47:09.900','Finshed',6)
insert into [Audit] values ('A3','2015-07-30 09:47:09.900','Completed',7)
insert into [Audit] values ('A3','2015-07-25 08:47:09.900','Succeeded',8)
insert into [Audit] values ('A3','2015-07-30 09:47:09.900','Succeeded',9)
insert into [Audit] values ('A4','2015-07-01 09:47:09.900','SQL Server relased',10)
insert into [Audit] values ('A4','2015-07-17 09:47:09.900','Failed Released',11)
insert into [Audit] values ('A4','2015-07-17 10:47:09.900','Still working on this',12)
Thanks
You'll also need to decide what actually constitutes "latest". I can assume that the createddate and the sequence will always agree, but with the ties for createddate, I have to then rely on sequence, and given that your sample data is accurate to 3 milliseconds, that's kind of odd that there would be such ties. If sequence and createddate will always stay in the same order, then you can use the following:
DECLARE @Audit AS TABLE (
id nvarchar(64) NOT NULL,
createdDate datetime NOT NULL,
comment nvarchar(max) NULL,
sequence bigint NULL
);
INSERT INTO @Audit VALUES ('A1','2015-07-26 09:47:09.900','Still working on this',1)
INSERT INTO @Audit VALUES ('A1','2015-07-27 09:47:09.900','Be right back',2)
INSERT INTO @Audit VALUES ('A1','2015-07-28 09:47:09.900','No Feed back',3)
INSERT INTO @Audit VALUES ('A1','2015-07-29 09:47:09.900','No one knows',4)
INSERT INTO @Audit VALUES ('A2','2015-07-28 09:47:09.900','Keeping Working',5)
INSERT INTO @Audit VALUES ('A2','2015-07-29 09:47:09.900','Finshed',6)
INSERT INTO @Audit VALUES ('A3','2015-07-30 09:47:09.900','Completed',7)
INSERT INTO @Audit VALUES ('A3','2015-07-25 08:47:09.900','Succeeded',8)
INSERT INTO @Audit VALUES ('A3','2015-07-30 09:47:09.900','Succeeded',9)
INSERT INTO @Audit VALUES ('A4','2015-07-01 09:47:09.900','SQL Server relased',10)
INSERT INTO @Audit VALUES ('A4','2015-07-17 09:47:09.900','Failed Released',11)
INSERT INTO @Audit VALUES ('A4','2015-07-17 10:47:09.900','Still working on this',12);
WITH MAX_VALUES AS (
SELECT A.id, MAX(A.createdDate) AS MAX_DATE,
MAX(A.sequence) AS MAX_SEQ
FROM @Audit AS A
GROUP BY A.id
)
SELECT A.id, A.sequence, A.createdDate, A.comment
FROM @Audit AS A
INNER JOIN MAX_VALUES AS MV
ON A.id = MV.id
AND A.createdDate = MV.MAX_DATE
AND A.sequence = MV.MAX_SEQ
ORDER BY A.id
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply