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.
| Author |
Topic |
|
trouble2
Constraint Violating Yak Guru
267 Posts |
 Posted - 2009-07-20 : 07:09:26
|
| I know this must probably have been asked 1000s of times, but I can't find it.I'm making an articles table with versioning, and I want to list the articles where only the most recent version of each article is shown.The table looks like this:ItemID _ ArticleID _ Version _ Title _ Content1 _ 1 _ 1 _ Title1 _ Content 1, version 12 _ 1 _ 2 _ Title1 _ Content 1, version 23 _ 1 _ 3 _ Title1 _ Content 1, version 34 _ 2 _ 1 _ Title2 _ Content 2, Version 15 _ 2 _ 2 _ Title2 _ Content 2, Version 2When I run SP called ListContent it should return:ItemID _ ArticleID _ Version _ Title _ Content3 _ 1 _ 3 _ Title1 _ Content 1, version 35 _ 2 _ 2 _ Title2 _ Content 2, Version 2 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-20 : 07:12:08
|
[code]select *from( select *, row_no = row_number() over (partition by ArticleID order by Version desc)) dwhere row_no = 1[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-07-20 : 08:29:05
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Top 1 from group