Rick cf Rick cf - 4 months ago 20
SQL Question

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

Answer

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.

Comments