SQL Aggregate In Subquery Without Duplicating Where

Question

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:

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') 
) 

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.

• Cannot use MIN or MAX on zone as those are not guaranteed to be in alphabetical order based off their corresponding seq number
• Cannot GROUP BY zone as that would change the MIN(seq) returned
• Cannot do the WHERE in just the outer query as the subquery would not get the right MIN(seq)
• Cannot do the WHERE just in the inner query as there might be duplicate seq numbers in other zones

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.

Thanks a lot for any ideas and help!

Answer 1

Have you looked using a common table expression (CTE)

A CTE is kind of like a reusable subquery.