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)