sql statement many to one

[Terris]

I have two tables one contains header information for a return form the other is the activities against the return.
There are multiple entries for the activities against one return. I only want to grab the oldest activity

header fields
creditno (joins to detail)
customer
part
status

detail field
lastactivitydate (only want the latest from the table)
Is there a way to do this in a sql query
Thanks
Terri

 

[tlbroadbent]

You can use a query with a correlated sub-query to get the latest date.

Select
h.creditno, h.customer, h.part, h.status,
d.detail, d.lastactivitydate
From HeaderTbl h
Join DetailTbl d
On h.creditno=d.creditno
Where d.lastactivitydate =
(Select Max(lastactivitydate) From DetailTbl
Where creditno=h.creditno) Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.