query to obtain files whose last status=2

[charlesg]

Hi folks,
This is my first posting to this forum and I hope someone will be able to help me out with this.
I have a table called FILETRANS in a DB.Essentially this table is to track the in-flow and out-flow of files in a documentation unit.
The schema of the table is as follows
Filetrans_id Pk int Auto
Files_id int
Filetransdate date
Filestatus_id int

A filestatus can either be 1(IN) or 2 (out)
I need a query that will give me a list of all files whose last recorded status is 2(out) and vice versa.
Any help will be greatly appreciated.
Regards
Charles

[galexyus]

How about:

SELECT Files_id
FROM FILETRANS t1
WHERE Filetransdate = (SELECT MAX(Filetransdate) FROM FILETRANS t2 WHERE t1.Files_id = t2.Files_id)
AND Filestatus_id = 2