title: SQL Aggregate In Subquery Without Duplicating Where
body: <p>So the below query works just fine, but not finding a way to not duplicate that where clause in the subquery and the outer one:</p>
  
  <pre><code>SELECT * FROM table WHERE --Cannot change this as it is hardcoded in ArcGIS layer definition, only can access WHERE
  
  objectid IN (
      SELECT objectid
      FROM table a
      RIGHT JOIN (
          SELECT id, MIN(seq) as seq, --zone
          FROM table b 
          WHERE zone IN ( 'ZONE2', 'ZONE3', 'ZONE4') GROUP BY id) c
      ON a.id = c.id      
      AND a.seq = c.seq
      --AND a.zone = c.zone
      WHERE zone IN ( 'ZONE2', 'ZONE3', 'ZONE4') 
  ) 
  </code></pre>
  
  <p>I essentially want to do something like the commented out pieces which would allow dropping the outer duplicated where clause... but that of course doesn't work because there is no group by.</p>
  
  <ul>
  <li>Cannot use MIN or MAX on zone as those are not guaranteed to be in alphabetical order based off their corresponding seq number</li>
  <li>Cannot GROUP BY zone as that would change the MIN(seq) returned </li>
  <li>Cannot do the WHERE in just the outer query as the subquery would not get the right MIN(seq)</li>
  <li>Cannot do the WHERE just in the inner query as there might be duplicate seq numbers in other zones</li>
  </ul>
  
  <p>We have about 15 variations of the zones in the WHERE clause and they will slowly change over time. So just trying to minimize the duplication which might help the long term reliability of switching them out.</p>
  
  <p>Thanks a lot for any ideas and help!</p>