April 22, 2015 at 2:27 pm
I have a table that holds Folders and the documents with a version number. What I am trying to do is list the folder, distinct documents and the max of the version number for the document.
Data:
Folder Document Nbr VersionNbr
--------- ----------------- -----------
1 Doc1 0
2 Doc2 0
2 Doc2 1
3 Doc3 0
3 Doc3 1
3 Doc4 0
Results:
Folder Document Nbr VersionNbr
--------- ---------------- -----------
1 Doc1 0
2 Doc2 1
3 Doc3 1
3 Doc4 0
Any help to point me in the right direction is greatly appreciated.
April 22, 2015 at 2:32 pm
ldummer (4/22/2015)
I have a table that holds Folders and the documents with a version number. What I am trying to do is list the folder, distinct documents and the max of the version number for the document.Data:
Folder Document Nbr VersionNbr
--------- ----------------- -----------
1 Doc1 0
2 Doc2 0
2 Doc2 1
3 Doc3 0
3 Doc3 1
3 Doc4 0
Results:
Folder Document Nbr VersionNbr
--------- ---------------- -----------
1 Doc1 0
2 Doc2 1
3 Doc3 1
3 Doc4 0
Any help to point me in the right direction is greatly appreciated.
You need to use the MAX aggregate function for this.
Select Folder
, [Document Nbr]
, MAX(VersionNbr)
FROM someTable
group by Folder
, [Document Nbr]
You should probably get in the habit now of not putting spaces in your column names. It is a pain to work with.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2015 at 2:33 pm
i think a subquery featuring row_nubmer would work here:
/*--Results
RWFolderDocument NbrVersionNbr
11Doc10
12Doc21
13Doc31
13Doc40
*/
;WITH MyCTE([Folder],[Document Nbr],[VersionNbr])
AS
(
SELECT 1,'Doc1',0 UNION ALL
SELECT 2,'Doc2',0 UNION ALL
SELECT 2,'Doc2',1 UNION ALL
SELECT 3,'Doc3',0 UNION ALL
SELECT 3,'Doc3',1 UNION ALL
SELECT 3,'Doc4',0
)
SELECT * FROM
(
SELECT row_number() over (partition by [Folder],[Document Nbr] order by [Folder],[Document Nbr],[VersionNbr] DESC) As RW,* FROM MyCTE
) MySubQuery WHERE RW = 1
Lowell
April 22, 2015 at 2:49 pm
Lowell that is exactly what I was looking for. Thanks
April 22, 2015 at 3:03 pm
ldummer (4/22/2015)
Lowell that is exactly what I was looking for. Thanks
Not that I care which style you prefer but the code I posted returns the exact same results. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply