Prev: Creating a New Project
Next: Extracting Image datatype content with SQL2005?
From: Aviast on 31 Jul 2010 08:38
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 :)
From: Gert-Jan Strik on 31 Jul 2010 08:57
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
 | 
Pages: 1
Prev: Creating a New Project
Next: Extracting Image datatype content with SQL2005?