title: SQL: Removing Duplicates rows while retaining the row with highest value in another column
body: <p>Suppose i have a table Test with Data:</p>
  
  <pre><code>SOID SO_Name   SO_Desc     PRIORITY  ADE_PRIORITIZED  DEPLOY_DATE  ENV
  123  SO1      SO1 Desc1      111      Y               01-JAN-01     0
  123  SO1      SO1 Desc1      111      Y               01-JAN-01     1
  123  SO1      SO1 Desc1      111      Y               01-JAN-01     2
  123  SO1      SO1 Desc1      111      Y               01-JAN-01     3
  987  SO1      SO1 Desc1      111      Y               01-JAN-01     0
  987  SO1      SO1 Desc1      111      Y               01-JAN-16     1
  987  SO1      SO1 Desc1      111      Y               21-JAN-17     2
  987  SO1      SO1 Desc1      111      Y               01-JAN-17     3
  121  SO121    SO121 Desc121  111      Y               01-JAN-17     0
  </code></pre>
  
  <p>I want to remove the duplicate rows for each soid (duplicate can be based on the 4 columns: so_name,so_desc,priority, ade_prioritized) retaining the row with highest deploy_date.</p>
  
  <p>I used this query but it doesn't delete any row.</p>
  
  <pre><code>delete from so_test a 
  where a.deploy_date &lt; (
    select max(b.deploy_date) from so_test b where a.soid = b.soid
  );
  
  0 rows deleted
  </code></pre>
  
  <p>The end result i expect should be:
  SOID SO_Name   SO_Desc     PRIORITY  ADE_PRIORITIZED  DEPLOY_DATE  ENV
  123  SO1      SO1 Desc1      111      Y               01-JAN-01     0
  987  SO1      SO1 Desc1      111      Y               21-JAN-17     2
  987  SO1      SO1 Desc1      111      Y               21-JAN-17     2</p>
  
  <p>What can be the issue? 
  can it be done without CTE?</p>