Subquery max sequence number
- by Andy Levesque
I'm hesitant to ask because I'm sure it's out there, but I just can't seem to come up with the keywords to find the answer. I'm stepping outside my boundaries by starting with subqueries (normally an Access user).
I have a query that has TECH_ID, SEQ_NBR, and PELL_FT_AWD_AMT
SELECT ISRS_V_NEED_ANAL_RESULT_PARENT.TECH_ID, ISRS_V_NEED_ANAL_RESULT_PARENT.AWD_YR, ISRS_V_NEED_ANAL_RESULT_PARENT.PELL_FT_AWD_AMT, ISRS_V_NEED_ANAL_RESULT_PARENT.SEQ_NBR
FROM ISRS_V_NEED_ANAL_RESULT_PARENT
GROUP BY ISRS_V_NEED_ANAL_RESULT_PARENT.TECH_ID, ISRS_V_NEED_ANAL_RESULT_PARENT.AWD_YR, ISRS_V_NEED_ANAL_RESULT_PARENT.PELL_FT_AWD_AMT, ISRS_V_NEED_ANAL_RESULT_PARENT.SEQ_NBR
HAVING (((ISRS_V_NEED_ANAL_RESULT_PARENT.AWD_YR)="2013"))
ORDER BY ISRS_V_NEED_ANAL_RESULT_PARENT.TECH_ID;
What I want to return is add a subquery that selects only the max SEQ_NUM for each record, but I can't seem to get the syntax right. In the past I would cheat and have a separate query that first gave me the TECH_ID and max SEQ_NUM, and then have a second query that use the original table and the first query in a join to get the rest.
How can I do this in one query?
Example:
TECH_ID SEQ_NUM PELL
1 1 4000
1 2 4000
1 3 5000
Using just the max of the sequence number still returns: 1; 2; 4000 and 1; 3; 5000 when I'm only wanting the latter.
