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

Hunky · #1 (**permalink**) 04-21-09, 05:59

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

r937 · #2 (**permalink**) 04-21-09, 07:10

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'

gvee · #3 (**permalink**) 04-21-09, 08:29

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

Hunky · #4 (**permalink**) 04-24-09, 01:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech