Selecting the top record in a group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting the top record in a group

Author

Topic

DavidDBUser
Starting Member

3 Posts

Posted - 2015-03-18 : 03:24:18

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.

I’ve spent ages playing about with SQL Server, checking forums, etc., and I believe this is possible, I just can’t work out how to do it.

Any help would be greatly appreciated! :)

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-18 : 06:18:35

You can achieve this by using:

ROW_NUMBER()OVER(PARTITION BY documentid ORDER BY majorversion desc) latestver

You can use a common table expression (cte) to return the single last row based on the ROW partition.

We are the creators of our own reality!

DavidDBUser
Starting Member

3 Posts

Posted - 2015-03-18 : 08:00:51

Thanks for the quick reply. I'm struggling to understand the full SQL statement I need. Could you please expand? Cheers.

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-18 : 08:38:42

If you are just trying to get the latest version say based on MajorVersion something like this would return the latest based on date if date is last update date for the version.

; With rankresult
As
(
select  ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name,
		ROW_NUMBER() OVER(PARTITION By Documentid ORDER BY yourdate desc) [latestver]
		From YourDB
		)
		Select * From rankresult
		Where [latestver] = 1

We are the creators of our own reality!

DavidDBUser
Starting Member

3 Posts

Posted - 2015-03-18 : 19:13:20

Thank you, this worked great! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources