How to Select Latest Version of Records

Question

Hello,

I'm trying to create the SQL statement to select the latest version of articles by joining two tables. We have a magazine that contains several articles and each article can contain a collection pages. This particular magazine has the capability to save multiple versions of any particular page of an article. I need some assistance on how to obtain the latest version of the blog pages. The column Version (integer) contains the version number, where the highest number is the latest version. Here are the two tables and a start of the SQL statement:

SELECT * FROM Articles Main 
INNER JOIN Article_Version_Page Join1 ON Join1.[ArticleID] = Main.[ArticleID] 
WHERE NOT Main.[IsDeleted]

Articles

ArticleID (PK, int, not null) Key
IsDeleted (bit, not null)

Article_Version_Page

ArticleID (PK, FK, int, not nul) Key
Version (PK, FK, int, not null) Key
PageID (PK, int, not null) Key
Title (nvarchar(255), not null)

Here is some data in both tables:

ArticleID	IsDeleted
1	0
2	1

ArticleID	Version	PageID	Title
1	1	100	2012 Events
1	1	101	2013 Events
1	2	100	2012 Upcoming Events
1	2	101	2013 Upcoming Events
1	3	100	2012 Latest Events
2	1	200	2012 News
2	1	201	2013 News
2	2	200	2012 Current News
2	2	201	2013 Latest News
2	3	200	2012 Current News Briefs
2	4	200	2012 Current News Highlights

What I would like to end up with is the following:

ArticleID	Version	PageID	Title	IsDeleted
1	2	101	2013 Upcoming Events	0
1	3	100	2012 Latest Events	0
2	2	201	2013 Latest News	0
2	4	200	2012 Current News Highlights	0

Any assistance is greatly appreciated.

Answer 1

SELECT top 1 with ties * FROM Articles Main 
INNER JOIN Article_Version_Page Join1 ON Join1.[ArticleID] = Main.[ArticleID] WHERE NOT Main.[IsDeleted]
order by row_number() over (partition by Join1.[ArticleID], Join1.PageID order by Join1.Version desc)

---

http://www.t-sql.ru

Answer 2

If you are using SQL Server 2005 or newer versions, the below script can be tried:

SELECT
	A.ArticleID, P.Version, P.PageID, P.Title, A.IsDeleted
FROM
	Articles A
INNER JOIN
	(
		SELECT *, RNK = ROW_NUMBER() OVER (PARTITION BY ArticleID, PageID ORDER BY Version DESC) 
		FROM Article_Version_Page
	) AS P
ON
	P.ArticleID = A.ArticleID
WHERE
	P.RNK = 1 AND NOT A.IsDeleted

---

Krishnakumar S

Answer 3

Krishnakumar,

That worked perfectly. I just had to make one minor alteration, which is the WHERE statement (P.RNK = 1 AND A.IsDeleted = 0)

Thank you very much for you assistance with this.

Best Regards,
Albert

Answer 4

Alexey,

Your answer worked great too. I just had to make one minor alteration, which is in the WHERE statement (Main.[IsDeleted] = 0)

Thank you very much for you assistance with this.

Best Regards,
Albert