467,134 Members | 968 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

Selecting the top 1 record from a related table

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
  1. SELECT p.plan_id, c.[date] AS latest_contact
  3. FROM plan p
  4. LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
  6. WHERE p.contact_id IN (
  7.         SELECT TOP 1 contact_id
  8.         FROM plan_contact c
  9.         WHERE c.plan_id = p.plan_id
  10.         ORDER BY [date] DESC
  11.     )
  13. ORDER BY plan_id ASC
Jan 13 '09 #1
  • viewed: 6373
7 Replies
Uncle Dickie
Will this do it for you?

Expand|Select|Wrap|Line Numbers
  1. SELECT p.plan_id, max(c.[date])
  2. FROM plan p
  3. LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
  4. GROUP BY p.plan_id
Jan 13 '09 #2
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).
Jan 13 '09 #3
Uncle Dickie
Here is another way that should get what you want. It might not be the best SQL...

Expand|Select|Wrap|Line Numbers
  2. p.plan_id
  3. ,(SELECT TOP 1 [date] FROM plan_contact c1 WHERE c1.plan_id = p.plan_id ORDER BY [date] DESC)
  4. ,(SELECT TOP 1 field2 FROM plan_contact c1 WHERE c1.plan_id = p.plan_id ORDER BY [date] DESC)
  5. ,(SELECT TOP 1 field3 FROM plan_contact c1 WHERE c1.plan_id = p.plan_id ORDER BY [date] DESC)
  7. FROM
  8. plan p LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
  10. ORDER BY
  11. p.plan_id
Jan 13 '09 #4
Expert 1GB
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
  1. SELECT p.plan_id,p.field2,p.field3,p.etc, max(c.[date]) 
  2. FROM plan p 
  3. LEFT JOIN plan_contact c ON c.plan_id = p.plan_id 
  4. 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
  1. select a.plan_id,a.Dte,a.field2,a.field3,a.etc,b.field1,b.field2,b.etc
  2. from
  3. (   SELECT p.plan_id,p.field2,p.field3,p.etc, max(c.[date]) as Dte
  4.     FROM plan p 
  5.     LEFT JOIN plan_contact c ON c.plan_id = p.plan_id 
  6.     GROUP BY p.plan_id 
  7. )a
  8. 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
Jan 13 '09 #5
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.
Jan 13 '09 #6
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. :)
Jan 13 '09 #7
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. :)
Jan 13 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by webhigh | last post: by
19 posts views Thread by davidgordon@scene-double.co.uk | last post: by
3 posts views Thread by John Fairhurst | last post: by
4 posts views Thread by Jeffrey Davis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.