sql server programming: Hello,

I'm having a bit of problems getting the following done in sqlserver 2000; I
have a table with the following fields:


ClientID | FileName | Revision | LocalPath
===================================================
27 | C:\xxx\layout.xml | 2 | C:\filepath1
23 | C:\xxx\layout.xml | 1 | C:\filepath2
27 | C:\xxx\layout.xml | 1 | C:\filepath3
23 | C:\xxx\layout.xml | 2 | C:\filepath4
23 | C:\uneeded.xml | 1 | C:\filepath5

What I want to do is get the ClientID, LocalPath columns for the latest
version of the file matching the pattern %layout.xml. The resulting output
of the query for the above shoudl return:


ClientID | LocalPath
======================
27 | C:\filepath1
23 | C:\filepath4


Any ideas? I appreciate any help on this. Thanks in advance.

-Don

Don Khan (don_khan@comcast.net) writes:
[quoted text, click to view]

SELECT a.ClientID, a.LocalPath
FROM tbl a
JOIN (SELECT CliendID, maxrev = MAX(Revision)
FROM tbl
GROUP BY ClientID) AS b ON a.ClientID = b.ClientID
a.Revision = b.maxrev

The thing in parentheses is a derived table. You can think of it
logically as a temp table within the query, but note that it may
never be computed as such. The optimizer often recasts computation
order for better performance. Derived tables is an extremely powerful
tool to build complex queries.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at