Author |
Topic |
razeena
Yak Posting Veteran
54 Posts |
Posted - 2011-01-28 : 05:40:02
|
ID DocName Version ParentDocID1 MyDoc1 1 NULL2 MyDoc2 2 13 MyDoc3 3 14 NewDoc 1 NULLI 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 ParentDocID3 MyDoc3 3 14 NewDoc 1 NULLAny 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 @SampleSELECT 1, 'MyDoc1', 1, NULL UNION ALLSELECT 2, 'MyDoc2', 2, 1 UNION ALLSELECT 3, 'MyDoc3', 3, 1 UNION ALLSELECT 4, 'NewDoc', 1, NULL;WITH cteSourceAS ( 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, ParentDocIDFROM cteSourceWHERE 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, NULLUNION ALLSELECT 2, 'MyDoc2', 2, 1UNION ALLSELECT 3, 'MyDoc3', 3, 1UNION ALLSELECT 4, 'NewDoc', 1, NULL)SELECT *from(SELECT Row_Number() Over(PARTITION BY IsNull(ParentDocID,id) ORDER BY version desc) AS nn, *FROM your_table) sWHERE nn=1Best regards,Devart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete |
|
|
|
|
|