Selecting the top matching record of a one to many relationship on a SQL join statement

Question

I have a question that will probably something simple but I am not a SQL expert by any stretch. I have this query that has 3 join, the 3rd one is a one to many relationship and I am trying to get the top 1. Which in this case would be the last edit on a support call.

The info comes for 4 tables, CASE, for the basic case details, CONTACT1 and CONTACT2 for the contacts information and NOTES for the notes of said case, This NOTES table creates 1 entry for each notes entered in that case. I want to create a dashboard that will include that last entry time stamp.

I tried to add a Select top (1) within the join, tried all possible options for the join and use Max (C3.MODIFIEDDATE) with Group By but I always end up with all the records in NOTES for each ticket.

Here is my Query:

SELECT c.NUMBER as [Case Number], c.OWNER as [Assigned To], case c.status when 0 then '<unknown>' when 1 then 'Assigned' when 2 then 'Reassigned' when 3 then 'Escalated' when 4 then 'Resolved' 
else cast(c.status as varchar(50)) end as [Case Status], c.SUBJECT as [Subject], c.OFFERING as [Next Step], DATEADD(day,DATEDIFF(day,0,c.CREATED_DATE),0) as [Created on], c1.CONTACT 
as [Primary Contact], c1.COMPANY as [Company], c3.MODIFIEDDATE as [Last Edit], c3.MODIFIEDBY as [Last Agent]

FROM CASES c left join(CONTACT1 c1 inner join CONTACT2 c2 on c1.ACCOUNTNO = c2.ACCOUNTNO ) on c1.ACCOUNTNO = c.ACCOUNTNO 
   left join NOTES as c3 on c3.LOPRECID =c.recid -- That's the one that has a 1 to many resolve


WHERE (  c.IS_TEMPLATE = 0  AND   c.STATUS IN ( 1,2,3 ) and c3.rectype='CS' )
ORDER BY [Company] asc, c3.modifieddate desc

Any suggestion would be apreciated

possible duplicate of [top 1 with a left join](http://stackoverflow.com/questions/2033699/top-1-with-a-left-join) — hatchet - done with SOverflow, Dec 16 '13 at 23:07

hatchet - done with SOverflow · Answer 1 · 2013-12-17T18:06:37.217

0

one way (there are others as well) replace

left join NOTES as c3 on c3.LOPRECID =c.recid

with

OUTER APPLY (SELECT TOP 1 MODIFIEDDATE, MODIFIEDBY 
             FROM NOTES AS N 
             WHERE N.LOPRECID = c.recid AND N.rectype='CS'
             ORDER BY MODIFIEDDATE DESC
) AS c3

and remove the c3.rectype='CS' from your WHERE clause.

You will need to ensure this gives the desired results in the case where there are no rows in NOTES that satisfy the rectype='CS' requirement, since that adds a little sand in the gears for what you're asking.

edited Dec 17 '13 at 18:06

answered Dec 16 '13 at 23:01

hatchet - done with SOverflow

15,919
5
47
57

1

The requestor wants the most recent row, so without an `ORDER BY` clause your query may return incorrect results. – ErikE Dec 17 '13 at 02:50
@ErikE - I had missed that in the question. Thanks for pointing it out. I've edited my answer. – hatchet - done with SOverflow Dec 17 '13 at 18:07

Aaron Bertrand · Answer 2 · 2013-12-16T23:43:39.453

0

LEFT JOIN 
(
   SELECT LOPRECID, MODIFIEDDATE, MODIFIEDBY, 
     rn = ROW_NUMBER() OVER (PARTITION BY LOPRECID ORDER BY MODIFIEDDATE DESC)
   FROM dbo.Notes
) AS C3
ON C3.LOPRECID = c.recid AND C3.rn = 1

edited Dec 16 '13 at 23:43

answered Dec 16 '13 at 23:09

Aaron Bertrand

272,866
37
466
490

Sorry I didn't get back to you guys earlier, I was expecting to get an email letting me know that people has responded to my questions. That worked, now I just need to test this in the application itself. I appreciate the help – user3108989 Jan 08 '14 at 22:13

Selecting the top matching record of a one to many relationship on a SQL join statement

2 Answers2