By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Selecting the top 1 record from a related table

AndrewB
P: 5
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
  2.  
  3. FROM plan p
  4. LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
  5.  
  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.     )
  12.  
  13. ORDER BY plan_id ASC
  14.  
Jan 13 '09 #1
Share this Question
Share on Google+
7 Replies


Uncle Dickie
P: 67
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
  5.  
Jan 13 '09 #2

AndrewB
P: 5
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
P: 67
Here is another way that should get what you want. It might not be the best SQL...

Expand|Select|Wrap|Line Numbers
  1. SELECT
  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)
  6.  
  7. FROM
  8. plan p LEFT JOIN plan_contact c ON c.plan_id = p.plan_id
  9.  
  10. ORDER BY
  11. p.plan_id
Jan 13 '09 #4

Delerna
Expert 100+
P: 1,134
@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
  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 
  5.  
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
  9.  
Obviously you will need to substitute field1, field2, etc with real field names.

Hope it helps
Jan 13 '09 #5

Uncle Dickie
P: 67
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

AndrewB
P: 5
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

AndrewB
P: 5
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.