pretty basic sql problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

pretty basic sql problem

Author

Topic

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2004-02-24 : 08:57:38

I am trying to get the most recent contact for a given record.
(here is a shortened table version)

Attorney tBL (A)
AID
AName

AttorneyContact TBL (AC)
ACID
AID
ContactDate

So ideally:
AID ACID ContactDate
3 44 '2/23/04'
43 32 '2/21/04'
77 65 '2/20/04'

an attorney can have many contacts but i need the most recent, this is simple, but i guess i haven't done any db work in a long time. How do I weed out the records so the aid only shows once with their given contact date?

any help, thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-02-24 : 09:03:54

select *
from AttorneyContact ac
where ac.ContactDate = (select max(ac2.ContactDate) from AttorneyContact ac2 where ac2.AID = ac.AID)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-24 : 09:36:26

alternative ....

select *
from AttorneyContact ac
where not exists (select 1 from AttorneyContact where ac.AID = AID and ac.ContactDate < ContactDate)

Jay White
{0}

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2004-02-24 : 12:25:12

Rock 'n,

I ended up using Page47's solution.

Cheers.

Want a good Pilsner? Try Southern Germany.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources