Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: retrieving document details with highest version among the documents of same name

    I have a table with the following fields and records

    DocName-Doctype-DocVersion-DocID
    ABC HR 1.0 1
    ABC HR 2.0 2
    XYZ HR 1.0 3
    XYZ HR 1.1 4
    PQR FM 1.0 5

    Now i have to write a query such that only DocName ABC and XYZ's highest version records get selected. The records are to be fetchd on a particular DocType (HR in this case)
    the result should b like this

    DocName-Doctype-DocVersion-DocID
    ABC HR 2.0 2
    XYZ HR 1.1 4

    Any kind of help is highly appreciated.
    Thanks in Advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.DocName
         , t.Doctype
         , t.DocVersion
         , t.DocID
      FROM ( SELECT DocName 
                  , MAX(DocVersion) AS max_version
               FROM daTable
             GROUP
                 BY DocName ) AS m
    INNER
      JOIN daTable AS t
        ON t.DocName = m.DocName
       AND t.DocVersion = m.max_version
     WHERE t.DocType = 'HR'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Would it not be more efficient to move the where clause in to the subquery?

    Note that this could return different results depending on the nature of your data: can one document name have more than one document type?

    If so, and assuming you have the data
    PQR HR 1.0 5
    PQR FM 2.0 6
    Would you want to return PQR in your result?

    If the answer is yes, then put there where clause in the subquery.
    If the answer is no, then leave it be
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2009
    Posts
    4
    Thanks r937...

    And for george..

    In the scenario that u have given.. It should show both PQR
    PQR HR 1.0 5
    PQR FM 2.0 6

    Bcoz here the document type is different and not same...
    How can i modify the query for that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •