Get latest record - 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

General SQL Server Forums

New to SQL Server Programming

Get latest record

Author

Topic

razeena
Yak Posting Veteran

54 Posts

Posted - 2011-01-28 : 05:40:02

ID DocName Version ParentDocID
1 MyDoc1 1 NULL
2 MyDoc2 2 1
3 MyDoc3 3 1
4 NewDoc 1 NULL

I have a table which saves document uploaded information with its version no.If an updated doc is saved, then its saved with the version no and the parentdocumentID.I need to get the latest version document information like this while searching the document.

ID DocName Version ParentDocID
3 MyDoc3 3 1
4 NewDoc 1 NULL

Any help is appreciated.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 05:49:41

I could not understand!!! What is the criteria for Latest Document that has been inserted? By the way i think latest means "The ONE" which is inserted at last. In your case your output is showing multiple rows thats a bit confusing for me!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-28 : 05:57:53

[code]DECLARE @Sample TABLE
(
ID INT,
DocName VARCHAR(20),
Version INT,
ParentDocID INT
)

INSERT @Sample
SELECT 1, 'MyDoc1', 1, NULL UNION ALL
SELECT 2, 'MyDoc2', 2, 1 UNION ALL
SELECT 3, 'MyDoc3', 3, 1 UNION ALL
SELECT 4, 'NewDoc', 1, NULL

;WITH cteSource
AS (
SELECT ID,
DocName,
Version,
ParentDocID,
ROW_NUMBER() OVER (PARTITION BY COALESCE(ParentDocID, ID) ORDER BY Version DESC) AS RecID
FROM @Sample
)
SELECT ID,
DocName,
Version,
ParentDocID
FROM cteSource
WHERE RecID = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

Devart
Posting Yak Master

102 Posts

Posted - 2011-01-28 : 06:04:18

Hello,

For example:

WITH your_table(ID, DocName, Version, ParentDocID)
AS
(
SELECT 1, 'MyDoc1', 1, NULL
UNION ALL
SELECT 2, 'MyDoc2', 2, 1
UNION ALL
SELECT 3, 'MyDoc3', 3, 1
UNION ALL
SELECT 4, 'NewDoc', 1, NULL
)

SELECT
*
from
(
SELECT
Row_Number() Over(PARTITION BY IsNull(ParentDocID,id) ORDER BY version desc) AS nn,
*
FROM your_table
) s
WHERE nn=1

Best regards,

Devart,
SQL Server Tools:
dbForge Data Studio
dbForge Schema Compare
dbForge Data Compare
dbForge SQL Complete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources