TOP 1 per group of same records.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TOP 1 per group of same records.

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2009-01-05 : 14:30:35

I would like to display top 1 record based on View_CommentsByLoginNameAndDate Date column for any TaskID. Basically if there are 2 commnets for TaskID = 400, I would need to display only one entry per TaskID, latest entry based on date. When I display the projects I need to show only 1 row per taskid in a table. Spent a lot of timen on this cannot figure this out. Any help is greatly appreciated.

I have a stored procedure like this:
ALTER PROCEDURE [dbo].[sp_DisplayAllProjectsWithorWithoutComments]

AS
BEGIN
select *,A2.FullName,A2.[Date], A2.Comments, A2.LoginName from view_AllProjects A1
LEFT OUTER JOIN View_CommentsByLoginNameAndDate A2 ON A1.TaskID = A2.TaskID
ORDER BY A2.LoginName,A2.[Date] DESC
END
--------------------------------------------------------
Output for View_CommentsByLoginNameAndDate looks like this:
LoginName FullName Date Comments TaskID
E327352 John Smith 2008-03-20 09:33:00 New Ous needed 400
E322352 Jim McDonald 2008-03-10 08:12:00 New Ous needed 400
E322352 John Smith 2008-02-20 09:20:00 new PR created 405
E777388 Jack Taylor 2008-01-20 09:20:00 new PR created 410

---------------------------------------------

view_AllProjects is created like this:

SELECT T1.TaskID, T1.Manager, T1.Lead, T1.WRM, T1.PAR, T1.PTDB, T1.TspanID, T1.SDMSRec, T1.PMO, T1.RequestingLOB, T1.LOB, T1.PropertyIDStartLoc,
T1.PropertyIDFinishLoc, T1.[Start Location Address], T1.[Start Location City], T1.[Start Location State], T1.Description, T1.ProjectType, T1.Activity,
T1.ServerName, T1.ServerType, T1.TechAnalyst, T1.Status, T1.RAG, T1.StartDates, T1.EstimatedCompletionDate, T1.ActualCompletionDate,
T1.TreeorDomainImpacted, T1.NumOfSites, T1.NumOfUsers, T1.GBdatamoved, T1.GBdatadeleted, T1.NumOfSrvrsAdded, T1.NumOfAppsDeployed,
T1.EUTEngineeringConsult, T1.Comments, T1.RequestID, T1.TimeSpend, T1.Complexity, T1.LastUpdated, T1.StatusCompletedDate,
T1.StatusCancelledDate, T1.CreatedDate, T3.PID, T3.PropertyID, T3.Name, T3.Address, T3.City, T3.State, T3.Address AS [Finish Location Address],
T3.City AS [Finish Location City], T3.State AS [Finish Location State]
FROM dbo.fnKPIStartLocations() AS T1 LEFT OUTER JOIN
dbo.RealEstate AS T3 ON T1.PropertyIDFinishLoc = T3.PropertyID

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-05 : 15:40:15

See if this works...

ALTER PROCEDURE [dbo].[sp_DisplayAllProjectsWithorWithoutComments]

AS
BEGIN
SELECT A1.TaskID, A2.FullName,A2.Date, A2.Comments, A2.LoginName
FROM view_AllProjects A1
LEFT OUTER JOIN View_CommentsByLoginNameAndDate A2 ON A1.TaskID = A2.TaskID
WHERE A2.Date = (SELECT max(Date) FROM View_CommentsByLoginNameAndDate A2 WHERE A1.TaskID = A2.TaskID)
ORDER BY A2.LoginName,A2.Date DESC
END

jszulc
Yak Posting Veteran

66 Posts

Posted - 2009-01-05 : 16:48:25

I think we are almost there.... Logic which fishes out the last date is functioning properly, however I am not getting all records from KPITBL (which has more records than Commnets TBL, more TaskIDs then CommnetsTbl. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 03:39:41

[code]
ALTER PROCEDURE [dbo].[sp_DisplayAllProjectsWithorWithoutComments]

AS
BEGIN
SELECT A1.TaskID, A2.FullName,A2.Date, A2.Comments, A2.LoginName
FROM view_AllProjects A1
LEFT OUTER JOIN View_CommentsByLoginNameAndDate A2 ON A1.TaskID = A2.TaskID
AND A2.Date = (SELECT max(Date) FROM View_CommentsByLoginNameAndDate A2 WHERE A1.TaskID = A2.TaskID)
ORDER BY A2.LoginName,A2.Date DESC
END
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 03:45:45

also


ALTER PROCEDURE [dbo].[sp_DisplayAllProjectsWithorWithoutComments]

AS
BEGIN
SELECT A1.TaskID, A2.FullName,A2.Date, A2.Comments, A2.LoginName
FROM view_AllProjects A1
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY TaskID ORDER BY Date DESC) AS Seq,* FROM View_CommentsByLoginNameAndDate) A2 
ON A1.TaskID = A2.TaskID
AND A2.Seq=1 
ORDER BY A2.LoginName,A2.Date DESC
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources