title: Select distinct rows with max value of another column
body: <p>I am querying a document management database where documents can have many versions. So my query below returns the same document multiple times because of the versioning.</p>
  
  <pre><code> SELECT distinct d.DOCNUM, d.DOCNAME,P.FolderName
      FROM DOCUMENT as d with (nolock)
      inner join Project_T as pt on d.docnum=pt.item_id
      inner join Projects p on pt.prj_id=p.prj_id
     where d.type = 'Personal' and d.owner like '%67360'
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>DOCNUM     | DOCNAME     | FOLDERNAME |
  -----------+----------+---------------+
  123         Article        Jonathan
  123         Article part1  Jonathan
  256         Meeting Notes  Jonathan
  5697        Memo           Jonathan
  </code></pre>
  
  <p>Ideally docnum 123 should only return once.</p>
  
  <p>I have joined the table that holds the versions for each document and am selecting the latest version <code>(MAX(h.version)</code> so my query should return unique document numbers per row. </p>
  
  <pre><code>    SELECT distinct d.DOCNUM, h.version, d.DOCNAME,P.FolderName
      FROM DOCUMENT as d with (nolock)
      inner join DOCHISTORY as h on h.DOCNUM = d.docnum
      inner join Project_T as pt on d.docnum=pt.item_id
      inner join Projects p on pt.prj_id=p.prj_id
     where d.type = 'Personal' and d.owner like '%67360'
     and d.SECURITY = 'P'
     AND h.VERSION = (SELECT MAX(x.version) FROM DOCHISTORY as X 
                      where x.docnum = d.DOCNUM) 
  </code></pre>
  
  <p>but this time I get the latest versions however, where docname is slightly different, it returns the same docnum multiple times. see docnum 123 below.</p>
  
  <pre><code>DOCNUM     | Version      | DOCNAME     | FOLDERNAME |
  -----------+--------------+-------------+------------+
  123          9              Article        Jonathan
  123          9              Article part1  Jonathan
  256          1              Meeting Notes  Jonathan
  5697         21             Memo           Jonathan
  </code></pre>
  
  <p>I need to display the docname column in my report. Is there a way aorund this please?</p>