Distinct with a MAX()?

  • 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.

  • 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/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell that is exactly what I was looking for. Thanks

  • 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