Selecting Top Record In A Group?

I’m writing a document management system. The documents themselves are created from the contents of a database. The database is SQL Server.

The database contains a table, like so:

ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name

Initially, the user will create a “V0.1” document. So the data would look something like

ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 0
MinorVersion = 1
Name = “My Document”

Thereafter, the user can create new versions as “0.2”, “0.3”, etc., or “1.0”, “1.1”, “2.0”, etc.

For example, a “2.1” document would be stored as:

ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 2
MinorVersion = 1
Name = “My Document”

The earlier versions will still exist on the database, but the latest version will be 2.1.

There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.

I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.

So, if the database contained the following records:

ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,0,1,My Document
1,1,1,0,2,My Document
1,1,1,0,3,My Document
1,1,1,1,0,My Document
1,1,1,2,0,My Document
1,1,1,2,1,My Document
1,1,2,0,1,My Second Document
1,1,2,0,2,My Second Document
1,1,2,0,3,My Second Document

My query should return:

ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,2,1,My Document
1,1,2,0,3,My Document

… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.