Interbase SQL statement using MAX as filter?

Question

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

This is strictly an Interbase question, and has nothing to do with Delphi. — Ken White, Jul 24 '14 at 16:48
I could not find `the max of the year` on your desired result. There are 3 rows for 2015 and two rows for 2016 and you are getting one row for 2015 and two for 2016. I think you need to explain a bit better that result — Jorge Campos, Jul 24 '14 at 16:57
Max of theyear only grouped on PM.PID. The other fields are irrelevant for grouping. — Rick cf, Jul 24 '14 at 19:36

score 0 · Answer 1 · answered Jul 24 '14 at 17:49

0

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 
GROUP BY PM.PID
HAVING PM.YEAR = MAX(PM.YEAR);

answered Jul 24 '14 at 17:49

caiohamamura

2,260
21
23

I thought the group by clause must contain all columns mentioned in the fields list therefore this solution will not even function. Also I do not want all columns to be aggregatly grouped with the PID as my desired result set shows. – Rick cf Jul 24 '14 at 19:07
Sorry, but I can't understand what you want, with the data you supplied this will return exactly what you mentioned. – caiohamamura Jul 24 '14 at 19:38
It does not work as written. I tried it. It fails because PM.GUID (primary key) and PM.MISCID (a foreign key) is not in the group by clause. Those two columns contain different values for the same PM.PID so grouping cannot occur on the PM.PID because the processor has no clue which PM.GUID/ PM.MISCID to pick for the group. This is basic SQL grouping concepts. I need those columns in the result set so I cannot exclude them. Thus the (Simple SQL Server) solution is a subselect (Select * from(Select)). – Rick cf Jul 25 '14 at 13:51

score 0 · Accepted Answer · answered Jul 25 '14 at 13:55

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.

Interbase SQL statement using MAX as filter?

2 Answers2