Interbase SQL statement using MAX as filter?

Is it possible to use a max statement to subselect certain rows from a query on the MAX of one column?

I tried several things that did not work. I tried the max statement. I also see that maybe Interbase does not support what I commonly do in SQL Server - subselects. I could be wrong. I think we are using Interbase XE3.

Statement:

SELECT PM.GUID, PM.PID,  
       PM.MISCID, 
       CAST((PM.YEAR) AS NUMERIC) AS THEYEAR 
  FROM PMASTER PM 
              INNER JOIN SEL SL 
              ON SL.LGID = PM.PID 
 ORDER BY PM.PID, 
          THEYEAR

Data Returned

1244    1    21    2013
3444    1    21    2014
9888    1    21    2015
3244    3    45    2014
5144    3    45    2015
6588    3    45    2016
3324    6    73    2014
5454    6    73    2015
6758    6    74    2016

I desire the max of the year column: Desired data returned:

9888    1    21    2015
6588    3    45    2016
6758    6    74    2016

Thanks

Rick

Solution

OK, I figured it out. Contrary to several statements found here on Stackoverflow and elsewhere on the web Interbase does support subselects. Here is my working solution:

SELECT PM.GUID, PM.PID, PM.MISCID, PM.YEAR FROM PMASTER PM
    INNER JOIN SEL SL ON SL.LGID = PM.PID 
WHERE AND PM.YEAR IN
    (SELECT MAX(PMS.YEAR) FROM PMASTER PMS WHERE PMS.PID = PM.PID)
ORDER BY PM.PID, PM.YEAR

Hope that helps someone else.