Getting One Record Per Group From A One-to-Many Jo

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)

Getting One Record Per Group From A One-to-Many Jo

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2011-09-13 : 14:31:50

I have two tables, Tickets and Responses, joined by TicketID. Each ticket can have multiple responses but I want to show only the most recent response based on Response_Date. This doesn't seem to be doing the trick because I'm still seeing multiple records for each ticket. Thanks

SELECT
T.TicketID,
T.Subject,
T.Date_Received,
T.Requestor_FName,
T.Requestor_LName,
R.Response,
R.Response_Date,

FROM
Tickets T
INNER JOIN
(
SELECT TicketID, Response,
MAX(Response_Date) AS Response_Date
FROM Responses
GROUP BY TicketID, Response
) AS R
ON
(R.TicketID = T.TicketID)

memorykills
Starting Member

18 Posts

Posted - 2011-09-13 : 15:33:39

SELECT *
FROM
(SELECT
T.TicketID,
T.Subject,
T.Date_Received,
T.Requestor_FName,
T.Requestor_LName,
R.Response,
R.Response_Date,
Sequence = ROW_NUMBER () OVER( Partition By T.TicketID ORDER BY R.Response_Date DESC)
FROM Repsonses R
INNER JOIN Tickets T ON R.TicketID = T.TicketID
) a
WHERE Sequence = 1

Kristen
Test

22859 Posts

Posted - 2011-09-13 : 15:37:10

[code]
SELECT T.*
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY TicketID
ORDER BY TicketID , Response_Date DESC
),
X.*
FROM
(
SELECT T.TicketID,
T.Subject,
T.Date_Received,
T.Requestor_FName,
T.Requestor_LName,
R.Response,
R.Response_Date
FROM Tickets AS T
JOIN Responses AS R
ON R.TicketID = T.TicketID
) AS X
) AS T
WHERE T_RowNumber = 1
ORDER BY Response_Date DESC
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources