September 22, 2014 at 5:33 am
Hello
Need some help to optimize one query, i can solve the problem using the Not In and subquery's but it doesn't look right...
So i have a table with comments, each comment belongs to a group and it has a date.
The query takes a date parameter, and for each group must return the comment from that date if there isn't any returns the most recent comment.
Some sample data, and the separate query's that i have:
GO
DECLARE @TempTable AS TABLE(id INT, grp INT, dt SMALLDATETIME, txt NVARCHAR(50))
DECLARE @DataComment SMALLDATETIME
SET @DataComment = DATEADD(day,-8,GETDATE())
INSERT INTO @TempTable
SELECT 1,1,DATEADD(day,-10,GETDATE()),'Comment 1' UNION ALL
SELECT 2,1,DATEADD(day,-9,GETDATE()),'Comment 2' UNION ALL
SELECT 3,1,DATEADD(day,-8,GETDATE()),'Comment 3' UNION ALL
SELECT 4,2,DATEADD(day,-8,GETDATE()),'Comment 4' UNION ALL
SELECT 5,2,DATEADD(day,-6,GETDATE()),'Comment 5' UNION ALL
SELECT 6,2,DATEADD(day,-6,GETDATE()),'Comment 6' UNION ALL
SELECT 7,3,DATEADD(day,-10,GETDATE()),'Comment 7' UNION ALL
SELECT 8,3,DATEADD(day,-6,GETDATE()),'Comment 8' UNION ALL
SELECT 9,3,DATEADD(day,-5,GETDATE()),'Comment 9'
--- Comments in the date
SELECT *
FROM @TempTable
WHERE dt = @DataComment ORDER BY id DESC
--- Last comment for each group
SELECT * FROM (
SELECT
[Num] = ROW_NUMBER() OVER(PARTITION BY grp ORDER BY dt DESC,id ASC)
,*
FROM @TempTable) TT
WHERE TT.Num = 1
Thanks
September 22, 2014 at 5:58 am
Second one looks OK. Something like this for the first:
WITH RowNos AS ( -- order within groups in descending date order
SELECT ROW_NUMBER() OVER (PARTITION BY grp ORDER BY dt DESC) RowNo, *
FROM @TempTable
)
, RowNos2 AS ( -- only keep most recent rows and those matching the input date
SELECT ROW_NUMBER() OVER (PARTITION BY grp ORDER BY RowNo ASC) RowNo2, *
FROM RowNos
WHERE RowNo = 1 OR dt = @DataComment
)
SELECT grp, txt
FROM RowNos2
WHERE RowNo2 = 1
John
September 22, 2014 at 7:57 am
Thanks
But it doesn't work, the final result of that query is:
Grp 1 - 2014/09/14 - Comment 3
Grp 2 - 2014/09/19 - Comment 5 / Should be - 2014/09/14 - Comment 4
Grp 3 - 2014/09/17 - Comment 9
In the Group 2, i have a date that matches the parameter date, so the Group 2..
Where clause says RowNo = 1 OR dt = @DataComment, so it returns always the ROwNO=1.
That was the my biggest issue since the begin, kind write an if, if didn't exist the date then pick the ROwNo=1.
Thanks
September 22, 2014 at 8:06 am
Well, I did say something like that! Just change the ASC to DESC - that should fix it.
John
September 22, 2014 at 8:16 am
Oh... Bad habits, someone helping, then we forget to think...:ermm:
Reversing the order, its obvious that if the date exists it will find it before the Row Num. 😀
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply