Tricky Select Needed

[Kim R]

Hi all,

I hope someone can help me with this. I have been racking my poor stupid brain for days over this. I need a way to be able to create a view of the data in a single table. Let's call it tblStatus. Here is a list of fields for the example:

StatusId (Primary Key for this table)
StatusTypeId (Foreign Key to a static list of status types)
EquipmentId (Foreign key linking to tblEquipment)
EndDate (Date that this record finishes/finished being in effect)

Now the purpose of tblStatus is to store records about the location and usage of a piece of equipment. The StatusTypeId field links into the type of usage the equipement is used for in the particular record. There is more data in the table but it does not affect the current example.

The resulting data I need is:

List of all StatusId and EquipmentId for only the LATEST (EndDate) for each EquipmentId.

StatusID	EquipmentID	EndDate	StatusTypeID
8427	14402		30-Sep-08		1
8148	14402		14-May-07		3
7669	14402		07-Jul-01		15
8272	14403		27-Feb-08		2
8287	14403		11-Sep-06		1
7998	14403		20-May-05		6
7670	14403		25-Dec-02		15

In the above example data only records 8427 and 8272 would be what I wanted returned.

Can anyone point me in the right direction please?

[Kim R]

Looks like the following code might work:

SELECT a.StatusID, a.equipmentid,b.endate, a.StatusTypeID

FROM table1 INNER JOIN 

(

SELECT equipmentid, max(enddate) as enddate FROM table1

GROUP BY equpimentid

) B

on a.equipmentid=b.equipmentid

WHERE a.endate=b.enddate