G'day,

I've got a feeling this is a dead-simple problem and I'm going to be
embarrassed by the answer, but here goes...

I have a table "tblDocumentVersion" which has these fields:
* "VersionID" which is the primary key
* "DocID" which is a foreign key pointing to tblDocument
* "Version" which is a textual representation of a version (not
always numerical in this case)

I want the DocID and VersionID of the highest Version of each
document.

So far I've got this:

SELECT DocID, max(Version) FROM tblDocumentVersion GROUP BY DocID;

But I just can't figure out how to add the corresponding VersionID
into the query. If I add it to the "GROUP BY" clause then I get
*every* version, not just the max().

OK, embarrass me

I don't think there is a solution that will embarrass you.

Try this:

SELECT DocID, Version, VersionID
FROM (
SELECT DocID, Version, VersionID
, ROW_NUMBER() OVER (PARTITION BY DocID ORDER BY Version DESC) AS rn
FROM tblDocumentVersion
) T
WHERE rn=1

--
Gert-Jan