Firebird group by retrieve extra data

Question

I have a table with 6 columns: id, a, b, c, d, e. id is primary key.

I am trying to retrieve distinct a, b, c, max(d) for that group, and e that is present in the same row as max(d) is (column "id" is not relevant for my query).

I tried this query:

SELECT a, b, c, MAX(d), e
FROM tablename
GROUP BY a, b, c;

but it gives me "Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).". If I add an extra GROUP BY e, it just gives me distinct a, b, c, e, with a MAX(d) for each, which is not what I need. I do understand why this happens, what I don't understand is how to make it do what I need...

Is a subquery the way to go? Could you write one for me?

The most frustrating thing is that my query would work in MySQL :(

Answer 1

SELECT t1.a,t1.b,t1.c,t1.d,t1.e
FROM tablename AS t1
INNER JOIN (SELECT a, b, c, MAX(d) d
            FROM tablename
            GROUP BY a, b, c
           ) AS t2
ON  t1.a = t2.a
AND t1.b = t2.b
AND t1.c = t2.c
AND t1.d = t2.d

Note that if multiple rows are tied for MAX(d), this query will return all of the rows of a,b,c with that d value.

I don't know how MySQL deals with duplicate rows in this scenario.