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_k***@comcast.net) writes:
Show quoteHide quoteSELECT 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.

master table and child table andfield concatenation and loop records ?
VIEW's ORDER BY columns necessary in column list
Question for T-SQL Gurus
FOREIGN KEY that REFERENCES a server login for a database?
How to get the record created date?