Get only max version - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get only max version

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 04:32:26

Hi,

Imagine a table like this (it's just an example):


DocID    Version    DocTitle
============================
1        1          Fiscal Report 2006
1        2          Fiscal Report 2006
2        1          Meeting Minutes 11/12/07
3        1          Sales Meeting 12/11/07
3        2          Sales Meeting 12/11/07
3        3          Sales Meeting 12/11/07
4        1          New programmer CV

DocID and Version are both primary keys.

Is it possible to build a query which would select only the highest version numbers for each document from the table?

So my desired return is:


DocID    Version    DocTitle
============================
1        2          Fiscal Report 2006
2        1          Meeting Minutes 11/12/07
3        3          Sales Meeting 12/11/07
4        1          New programmer CV

Cheers,
Matt

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-11-26 : 04:35:55

something like.......

select docid, max(version) as version, doctitle
from yourtable
group by docid, doctitle
order by docid

Duane.

mattt
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 04:39:41

Hi,

That's great, thanks.

I've discovered this also works:


select DocId, version, DocTitle from documentrepository r1 where version = (select max(version) from documentrepository r2 where r2.documentid = r1.documentid)

which is likely to be the more efficient?

Cheers,
Matt

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-11-26 : 04:44:07

I would imagine that the group by one is more efficient, but it doesn't hurt to experiment a bit, now doesn't it?

Duane.

mattt
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 04:49:58

Thanks very much for your help so far.

This is going to sound really dense (because it is) but how do it go about testing?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-11-26 : 04:57:28

many ways,
in this case the easiest way would be to run it against a large chunk of data.

other places to start if you are interested in improving performance of queries is by looking at the execution plan (it's available from management studio on the toolbar or under Query on the menu - or by pressing Ctrl + L), before using this you would need to understand what all the bits and pieces on this query plan mean. There are many articles available on this subject - Just Google it (I should trade mark that phrase :))

Duane.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-11-26 : 06:17:02

Declare @t1 table(docid int,version int,doctitle varchar(100))
Insert into @t1(docid,version,doctitle)
SELECT 1 ,1 ,'Fiscal Report 2006' UNION ALL
SELECT 1, 2, 'Fiscal Report 2006' UNION ALL
SELECT 2, 1, 'Meeting Minutes 11/12/07' UNION ALL
SELECT 3 ,1 ,'Sales Meeting 12/11/07' UNION ALL
SELECT 3, 2 ,'Sales Meeting 12/11/07' UNION ALL
SELECT 3 ,3,'Sales Meeting 12/11/07'UNION ALL
SELECT 4, 1, ' New programmer CV'

If you want to go by the new SQL 2005 syntax then

select * from(select docid,max(version) as version,doctitle,row_number() over(partition by docid order by docid desc )as rowid
from @t1 group by docid,doctitle)as t1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources