February 25, 2009 at 5:26 pm
I have a table called ProjectComments which has fields - ID, Comments, ProjID, EmpID, DateSubmitted.
One projectID can have multiple comments submitted on different or same dates and time.
The idea is to come up with query to select last five comments for each project.
I did this using a cursor. But it is taking about 7 minutes to execute.
Can you guys direct me to some effecient way of doing this?
Thanks!
February 25, 2009 at 6:02 pm
Cant you simply create a derived table and join back to your main table (correlated subquery), the derived to include top 5 order by the date desc?
February 25, 2009 at 6:27 pm
The problem is I need this for every project ID...When I do top 5 only the 5 columns for one projID shows up
February 25, 2009 at 6:40 pm
Maybe this will help get you going???
CREATE TABLE Proj
(ProjId INT
,ProjDt DATETIME
)
go
CREATE TABLE ProjComments
(PCID int
,ProjID int
,Comment varchar (400)
,CommentDt datetime
)
INSERT INTO Proj VALUES (1,GETDATE()), (2, '1/1/09')
INSERT ProjComments VALUES (100,1,'COMMENT', '2/1/09'), (200,1,'COMMENT2', '3/1/09')
INSERT ProjComments VALUES (100,2,'COMMENT', '1/1/1988'), (200,2,'COMMENT2', '2/1/2000')
SELECT P.ProjId, X.Comment, X.CommentDt
FROM Proj P
INNER JOIN
(SELECT TOP 5 *
FROM ProjComments pc
ORDER BY ProjID DESC) X
ON P.ProjId = X.ProjID
ORDER BY 1,3 DESC
1COMMENT22009-03-01 00:00:00.000
1COMMENT2009-02-01 00:00:00.000
2COMMENT22000-02-01 00:00:00.000
2COMMENT1988-01-01 00:00:00.000
February 25, 2009 at 7:43 pm
This again goes back to listing only 5 records. What I want is last 5 comments for every projectid in the table. Three are about 10,000 records in ProjectComments table.
February 25, 2009 at 8:57 pm
Oh ok gotcha. Probably 100 ways to do it, here's my quick try. Use this and come up with something better. Now that I look at I almost deleted and try again but it's beer 30.
Take care
CREATE TABLE Proj
(ProjId INT
,ProjDt DATETIME
)
go
CREATE TABLE ProjComments
(PCID int
,ProjID int
,Comment varchar (400)
,CommentDt datetime
)
INSERT INTO Proj VALUES (1,GETDATE()), (2, '1/1/09')
INSERT ProjComments VALUES (100,1,'COMMENT', '2/1/09')
, (200,1,'COMMENT2', '3/1/09')
, (300,1,'COMMENT3', '3/1/09')
, (400,1,'COMMENT4', '3/2/09')
, (500,1,'COMMENT5', '3/3/09')
, (600,1,'COMMENT6', '3/4/09')
, (700,1,'COMMENT7', '3/5/09')
INSERT ProjComments VALUES (100,2,'COMMENT', '1/1/1988'), (200,2,'COMMENT2', '2/1/2000')
SELECT *
FROM (
SELECT ProjID, COMMENT, ROW_NUMBER() OVER (partition by projid order by commentdt DESC)as line
FROM ProjComments pc
GROUP BY ProjID, CommentDt, comment
) X
JOIN ProjComments C
ON C.ProjID = X.ProjID
AND C.Comment = X.Comment
WHERE line BETWEEN 1 AND 5
order by 1,CommentDt desc
February 25, 2009 at 9:01 pm
you'll want to use the Row_Number() function featuring the Partition parameters:
CREATE TABLE Proj
(ProjId INT
,ProjDt DATETIME
)
go
CREATE TABLE ProjComments
(PCID int
,ProjID int
,Comment varchar (400)
,CommentDt datetime
)
INSERT INTO Proj
select 1,GETDATE() union all
select 2, '1/1/09'
INSERT ProjComments
select 100,1,'COMMENT', '2/1/09' union all
select 200,1,'COMMENT2', '3/1/09' union all
select 100,2,'COMMENT', '1/1/1988'union all
select 200,2,'COMMENT2', '2/1/2000'
select * from
(
select P.ProjId, X.Comment, X.CommentDt,row_number() over (PARTITION BY X.Comment ORDER BY P.ProjId) AS RW
FROM Proj P
INNER JOIN ProjComments X
ON P.ProjId = X.ProjID ) Z
WHERe RW <=5
Lowell
February 26, 2009 at 1:02 am
I guess it should be
(partition by projid order by commentdt DESC)
Lowell (2/25/2009)
select * from
(
select P.ProjId, X.Comment, X.CommentDt,row_number() over (PARTITION BY X.Comment ORDER BY P.ProjId) AS RW
FROM Proj P
INNER JOIN ProjComments X
ON P.ProjId = X.ProjID ) Z
WHERe RW <=5
[/code]
John Smith
February 26, 2009 at 5:16 am
So, any of these working for you? I said 100...but if not, you now have 97 left to receive. 😉
February 27, 2009 at 2:37 pm
No..still not getting what i wanted to...
February 27, 2009 at 3:33 pm
barunpathak (2/27/2009)
No..still not getting what i wanted to...
Okay, so what have you tried and why does it not work? Read the article in my signature and post some create statements, insert statements and expected output (as outlined in the article) and I am sure someone here can come up with a solution.
The row_number() solution should work - but, you can also look at using CROSS APPLY. Read the article on this site at: http://www.sqlservercentral.com/articles/T-SQL/63115/ which shows how someone else did exactly what you are looking to do.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 1, 2009 at 10:53 pm
I'd do it this way... 96 left 😉
Probably some clever way to do this with CTE as well..
create table pr_ts
(
ID INT identity(1,1) primary key,
Comments VARCHAR(50),
ProjID INT,
EmpID INT,
DateSubmitted DATETIME default getdate()
)
go
insert pr_ts(comments,projid,empid)
select 'aaa',1,23
insert pr_ts(comments,projid,empid)
select 'aba',2,23
insert pr_ts(comments,projid,empid)
select 'aaza',1,23
insert pr_ts(comments,projid,empid)
select 'aaad',1,23
insert pr_ts(comments,projid,empid)
select 'aaca',2,23
go
create function F_Get_Last5(@projid int)
returns @table table
(
Comments VARCHAR(50),
ProjID INT,
EmpID INT,
DateSubmitted DATETIME
)
as
begin
insert @table
SELECT top 5 comments,Projid,empid,DateSubmitted
from pr_ts
where Projid = @projid
order by datesubmitted desc
return
end
go
select x.*
from (select distinct projid p from pr_ts)a
cross apply F_Get_Last5(a.p) x
HTH
"Who then will explain the explanation? Who then will explain the explanation?" Lord Byron
March 2, 2009 at 11:33 am
Outer APPLY worked guys. Thats a bunch for your help.
March 3, 2009 at 7:09 pm
barunpathak (3/2/2009)
Outer APPLY worked guys. Thats a bunch for your help.
Two way street here... post your final code so we can enjoy it, please. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 3, 2009 at 8:04 pm
barunpathak (3/2/2009)
Outer APPLY worked guys. Thats a bunch for your help.
I know you said you had a solution, but I think you should also give the following a try:
CREATE TABLE dbo.Proj
(ProjID INT
,ProjDt DATETIME
);
go
CREATE TABLE dbo.ProjComments
(PCID int
,ProjID int
,Comment varchar (400)
,CommentDt datetime
);
INSERT INTO dbo.Proj VALUES (1,GETDATE())
INSERT INTO dbo.Proj VALUES (2, '1/1/09');
INSERT dbo.ProjComments VALUES (100,1,'COMMENT', '2/1/09');
INSERT dbo.ProjComments VALUES (200,1,'COMMENT2', '3/1/09');
INSERT dbo.ProjComments VALUES (100,2,'COMMENT', '1/1/1988');
INSERT dbo.ProjComments VALUES (200,2,'COMMENT2', '2/1/2000');
with ProjectComments (
ProjCommentNum,
PCID,
ProjID,
Comment,
CommentDt
) as (
select
row_number() over (partition by ProjID order by CommentDt desc),
PCID,
ProjID,
Comment,
CommentDt
from
dbo.ProjComments
)
select
prj.ProjID,
prj.ProjDt,
prjc.Comment,
prjc.CommentDt
from
dbo.Proj prj
inner join ProjectComments prjc
on (prj.ProjID = prjc.ProjID
and ProjCommentNum between 1 and 5);
drop table dbo.Proj;
drop table dbo.ProjComments;
Please let us know how this works, and be sure to post back your solution as well.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply