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.

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?

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...

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]

I also need to pull other fields from both tables (such as the person who made the contact, the plan holder's details etc).

For that you could use uncle dickies code and add in any extra fields that you require from the plan table
[code=sql]
SELECT p.plan_id,p.fie ld2,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
[/code]

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

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. :)