title: AWS Athena SQL to group and find minimum in distinct rows
body: <p>I have a query against AWS Athena and the core of it works great. My companies code is AA (field ACD) and our competitors codes are BB, CC and DD (field OCD). So for each distinct trip my company makes I get a series of similar trips from competitors. I end up with a table like this:</p>
  
  <p>main = </p>
  
  <pre><code>AID ATRIPDT     ACD ACAR    CY1 CY2 OID OTRIPDT     OCD BCAR DELMN
  0   10/30/2018  AA  XX22    LAS LAX 300 10/30/2018  BB  ZZ1 21
  0   10/30/2018  AA  XX22    LAS LAX 544 10/30/2018  CC  T09 36
  0   10/30/2018  AA  XX22    LAS LAX 755 10/30/2018  BB  KLQ 57
  0   10/30/2018  AA  XX22    LAS LAX 912 10/30/2018  DD  75Q 5
  
  1   10/30/2018  AA  P700    LAS LAX 390 10/30/2018  BB  MNZ 13
  1   10/30/2018  AA  P700    LAS LAX 603 10/30/2018  BB  JJ1 30
  </code></pre>
  
  <p>However, the last step is to group by AID and select only one record for each OCD which should be the minimum value of DELMN.</p>
  
  <p>In this case I am looking for this as a result:</p>
  
  <pre><code>AID ATRIPDT     ACD ACAR    CY1 CY2 OID OTRIPDT     OCD BCAR DELMN
  0   10/30/2018  AA  XX22    LAS LAX 300 10/30/2018  BB  ZZ1 21
  0   10/30/2018  AA  XX22    LAS LAX 544 10/30/2018  CC  T09 36
  0   10/30/2018  AA  XX22    LAS LAX 912 10/30/2018  DD  75Q 5
  
  1   10/30/2018  AA  P700    LAS LAX 390 10/30/2018  BB  MNZ 13
  </code></pre>
  
  <p>I tried this</p>
  
  <pre><code>with main as
  (
      &lt;complex query that returns main table&gt;
  )
  select * from main
  where DELMN = (select min(DELMN) from main as b where b.OCD=main.OCD
  </code></pre>
  
  <p>which returns a total of three records so I am not setting up the grouping correctly. Am brain drained so not sure what else to try.</p>