Find max value in a group in FileMaker

Question

How to select only max values in a group in the following set

id  productid  price  year
---------------------------
1   11         0,10   2015
2   11         0,12   2016
3   11         0,11   2017
4   22         0,08   2016
5   33         0,02   2016
6   33         0,01   2017

Expected result for each productid and max year would be

id  productid  price  year
---------------------------
3   11         0,11   2017
4   22         0,08   2016
6   33         0,01   2017

score 3 · Accepted Answer · answered Dec 17 '17 at 17:39

This works for me.

ExecuteSQL (
"SELECT t.id, t.productid, t.price, t.\"year\"
    FROM test t
    WHERE \"year\" = 
        (SELECT MAX(\"year\") FROM test tt WHERE t.productid = tt.productid)"
; "  " ; "")

Adapted from this answer: https://stackoverflow.com/a/21310671/832407

score 0 · Answer 2 · answered Dec 17 '17 at 17:37

A simple SQL query will give you a last year for every product record

ExecuteSQL ( 
            "SELECT productid, MAX ( \"year\") 
             FROM myTable
             GROUP By productid";
 "";"" )

To get to the price for that year is going to be trickier, as FileMaker SQL does not fully support subqueries or temp tables.

Find max value in a group in FileMaker

2 Answers2