Tricky Select Needed
 

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?

Re: Tricky Select Needed
 

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