Prev: SQL Assistance required
Next: List Columns in a Table in SQL 2005
From: janurik.andras on 2 Apr 2008 04:49
Hi,

I store data about documents in a table.
The documents belong to company employees, and have two attributes:
document type and expiry date.
So the table structure: DocID, EmployeeID, DocumentType, ExpDate

When a document expires (or will expire soon) a new one is added with
a new exp date.
So there can be several documents of the same type for an employee,
but with different exp date.

I need to write a query to select documents, but only with the latest
exp date for each type and employee.
I need to list documents for every employee and every document type,
but if there are several documents of the same type for an employee, I
need to list only the one with the latest exp date.

Can anyone help with it, please?

Thanks,
Andras
From: Plamen Ratchev on 2 Apr 2008 08:45
Try:

SELECT DocID, EmployeeID, DocumentType, ExpDate
FROM Documents AS A
WHERE ExpDate = (SELECT MAX(B.ExpDate)
FROM Documents AS B
WHERE B.EmployeeID = A.EmployeeID
AND B.DocumentType = A.DocumentType);

On SQL Server 2005:

SELECT DocID, EmployeeID, DocumentType, ExpDate
FROM (SELECT DocID, EmployeeID, DocumentType, ExpDate,
ROW_NUMBER() OVER(PARTITION BY EmployeeID, DocumentType
ORDER BY ExpDate DESC) AS
seq
FROM Documents) AS T
WHERE seq = 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: SQL Assistance required
Next: List Columns in a Table in SQL 2005