title: How to get all orders with maximum update_count value only?
body: <p>I have laboratory orders system and these orders can be update more than one time for example </p>
  
  <pre><code>order_id     test_id    update_count
  10              1            1
  10              1            2 
  10              1            3 
  11              2            1
  11              5            1
  12              3            1 
  12              3            2 
  </code></pre>
  
  <p>I want to select all orders without duplicates and select orders with maximum update count </p>
  
  <p>I tried a lot and i checked WITH clients as and self join but always select statement returned all rows with all update counts not only maximum update count for each order_id</p>
  
  <p>This is my Select statement:</p>
  
  <pre><code>SELECT LAB_RESULTS.ORDER_ID as 'Order Number'
        ,LAB_RESULTS.PATIENT_NO as 'Patient No'
        ,Patients.Patient_Name as 'Patient Name'
        ,Patients.Age as 'Patient Age'
        ,LabTests.TestName as 'Test Name'
        ,LAB_RESULTS.RESULT_NUMBER as 'Result'
        ,LAB_RESULTS.RESULT_REPORT as 'Text Result'
        ,LAB_RESULTS.APPROVED_DATE as 'Approved_Date'
        ,LAB_RESULTS.REQ_FORM_NO as 'Request Form Number'
        ,LAB_RESULTS.CUSTID as 'Customer Id'
        ,Machines.Machine_name as 'Machine Name'
        ,LAB_RESULTS.SAMPLE_ID as 'Sample Id'
        ,LAB_RESULTS.packageid as 'package id'
        ,LAB_RESULTS.GROUPID as 'group id'
        ,LAB_RESULTS.EXAMINED_BY as 'Examined By'
        ,LAB_RESULTS.EXAMINED_DATE as 'Examined Date'
        ,LAB_RESULTS.APPROVED_BY as 'Approved By'
        ,LAB_RESULTS.update_count
  
  
  FROM LAB_RESULTS 
  
    inner join patients on LAB_RESULTS.patient_no = Patients.Patient_No
    inner join labtests on  LabTests.TestId = LAB_RESULTS.TESTID
    inner join Machines on Machines.Machine_id = LAB_RESULTS.machine_id
  
  where LAB_RESULTS.APPROVED_BY is not null 
  and LAB_RESULTS.SAMPLE_STATUS = 6
  and LAB_RESULTS.update_count in (select max(update_count) from LAB_RESULTS where LAB_RESULTS.SAMPLE_STATUS = 6 and LAB_RESULTS.deptid = 2 )
  and LAB_RESULTS.deptid = 2
  </code></pre>
  
  <p>I expect to get the following result : </p>
  
  <pre><code>order_id     test_id    update_count
  10              1            3 
  11              2            1
  11              5            1
  12              3            2 
  </code></pre>
  
  <p>then i added this condition but this return only the maximum update count only not group by each order_id only the maximum for all orders.</p>
  
  <pre><code>and LAB_RESULTS.update_count in (select max(update_count) from LAB_RESULTS where LAB_RESULTS.SAMPLE_STATUS = 6 and LAB_RESULTS.deptid = 2 )
  </code></pre>
  
  <p>How can I do this ?</p>