Selecting the top 1 record from a related table

AndrewB

Hi guys,

I tried to search but it seems there's a problem with the search engine at the moment. I'm suffering a bit of a brain freeze and hopefully someone can help me out with this...

I have two tables I'm focusing on, one contains plans and the second contains contact made with the holders of those plans. I want to pull out all plans, and for each plan - look up the date of the latest contact (including plans where no contact has been made, i.e. no records exist for that plan in the contact table).

Can anyone suggest the best way of doing this? I've found the following snippet online which is close, but it excludes plans where no contact records exist. Thanks in advance for any advice you can give.

Expand|Select|Wrap|Line Numbers

 
SELECT p.plan_id, c.[date] AS latest_contact
 
FROM plan p

LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
 
WHERE p.contact_id IN (

        SELECT TOP 1 contact_id

        FROM plan_contact c

        WHERE c.plan_id = p.plan_id

        ORDER BY [date] DESC

    )
 
ORDER BY plan_id ASC

Uncle Dickie

Will this do it for you?

Expand|Select|Wrap|Line Numbers

 
SELECT p.plan_id, max(c.[date])

FROM plan p

LEFT JOIN plan_contact c ON c.plan_id = p.plan_id

GROUP BY p.plan_id

AndrewB

Thanks Dickie, I'll give that a shot, but I think I've tried something along those lines before - I should have put in the example that I also need to pull other fields from both tables (such as the person who made the contact, the plan holder's details etc).

Uncle Dickie

Here is another way that should get what you want. It might not be the best SQL...

Expand|Select|Wrap|Line Numbers

 SELECT

p.plan_id

,(SELECT TOP 1 [date] FROM plan_contact c1 WHERE c1.plan_id = p.plan_id ORDER BY [date] DESC)

,(SELECT TOP 1 field2 FROM plan_contact c1 WHERE c1.plan_id = p.plan_id ORDER BY [date] DESC)

,(SELECT TOP 1 field3 FROM plan_contact c1 WHERE c1.plan_id = p.plan_id ORDER BY [date] DESC)
 
FROM

plan p LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
 
ORDER BY

p.plan_id

Delerna

@AndrewB
For that you could use uncle dickies code and add in any extra fields that you require from the plan table

Expand|Select|Wrap|Line Numbers

 
SELECT p.plan_id,p.field2,p.field3,p.etc, max(c.[date]) 

FROM plan p 

LEFT JOIN plan_contact c ON c.plan_id = p.plan_id 

GROUP BY p.plan_id

now to get the extra fields from the plan contract table, wrap the above up as a derived table (subquery) and join to the plan contract table using the id and the date and then select the fields you want

Expand|Select|Wrap|Line Numbers

 
select a.plan_id,a.Dte,a.field2,a.field3,a.etc,b.field1,b.field2,b.etc

from

(   SELECT p.plan_id,p.field2,p.field3,p.etc, max(c.[date]) as Dte

    FROM plan p 

    LEFT JOIN plan_contact c ON c.plan_id = p.plan_id 

    GROUP BY p.plan_id 

)a

join plan_contact b on a.plan_id=b.plan_id and a.Dte=b.date

Obviously you will need to substitute field1, field2, etc with real field names.

Hope it helps

Uncle Dickie

Hi Delerna, I'm not sure if this is relevant to AndrewB, but if you have more than one contact made on the same date, would you end up with multiple results for the same plan id?
My second option would avoid that but it would be random as to which record was picked for that particular date.
I guess if [date] includes a time stamp it is unlikely there would be two records that are identical.

AndrewB

Thanks for your help guys, I felt pretty idiotic asking as I work in SQL a fair bit, but was just having a bad afternoon. I'm at home at the moment, but I'll try the ideas you've posted as soon as I get into the office tomorrow. :)

AndrewB

Looking at this with fresh eyes this morning, I think I've nailed it using the examples from both of you. I ended up using Delerna's final example - I didn't realise there would be some entries with identical dates (from a legacy system import, I think), so I changed it to select the MAX id number from the contact table. This seems to be working well, thanks very much. :)

Selecting the top 1 record from a related table

Post your reply

Similar topics