Help with query

Two tables:

TBL1
HID NAME
-- ---
1 C1
2 C2
3 C3
4 C4

TBL2
CID HID CStartdate CEndDate
-- -- ---- ----
1 1 01 Jan 02 31 Dec 02
2 1 01 Jan 03 31 Dec 03
3 1 01 Jan 04 31 Dec 04
4 2 01 Jan 03 31 Dec 03
5 3 01 Jan 00 31 Dec 00
6 3 01 Jan 01 31 Dec 01
7 3 01 Jan 02 31 Dec 02
8 4 01 Jan 03 31 Dec 03

I'm looking for the query that will bring back the rows with the latest enddates for each of the HIDs, i.e

HID Name CStartdate CEndDate
-- --- ---- ----
1 C1 01 Jan 04 31 Dec 04
2 C2 01 Jan 03 31 Dec 03
3 C3 01 Jan 02 31 Dec 02
4 C4 01 Jan 03 31 Dec 03

Obviously a JOIN but also somewhere a group by on HID with max(Cenddate)? I'm having no luck, would appreciate some help...

Thanks
GregSomething along these lines?

USE Northwind
GO
SELECT *
FROM Orders o
INNER JOIN [Order Details] d
ON o.OrderId = d.OrderId
WHERE EXISTS (SELECT *
FROM Orders x
GROUP BY OrderId
HAVING MAX(x.OrderDate) = o.OrderDate
AND x.OrderId = o.OrderId)
GO|||Try something like this:

select t1.HID,t1.NAME,max(t2.CStartdate), max(t2.CEndDate)
from TBL1 t1
join TBL2 t2 on t2.HID=t1.HID
group by t1.HID,t1.NAME

If CStartdate<>EndDate for record - it needs to change logic...|||I think the second query is nearly there. However, Startdate is not equal to enddate, and hypothetically for a particular HID there may be an endate entry which isn't the latest but has a startdate later than the startdate for the entry with the latest enddate. This is extremely unlikely, but I want to make sure the query is robust.

Basically the first table is a list of individual hardware items, each referenced by a single HID.

The second table is a list of support contracts for the hardware. Over a period of time there will be multiple support contracts for each item, each covering a specific period of time (startdate/enddate).

I want the query to return me information about the hardware (from the first table) with information on the latest support contract. I thought a table join querying on the Max(enddate) and grouping by HID would work, but can't quite get there.......my logic generates errors :-)

g.|||Something like this maybe?

select tbl2.HID, tbl1.Name, tbl2.CStartDate, tbl2.CEndDate
from tbl2 inner join (select HID, Max(CEndDate) as EndDate from tbl2 group by HID) as Temp1
on tbl2.HID = Temp1.HID
inner join tbl1 on tbl2.HID = tbl1.HID
where tbl2.CEndDate = Temp1.EndDate|||That looks to have done the trick.
Cheers!

G.