Q:

Usage of GROUP BY and MIN with HSQLDB

I need to execute a query for retrieving the item with the soonest expire date for each customer. 
I have the following product table:
PRODUCT
 |ID|NAME |EXPIRE_DATE|CUSTOMER_ID
  1 |p1   |2013-12-31 |1
  2 |p2   |2014-12-31 |1
  3 |p3   |2013-11-30 |2

and I would like to obtain the following result:
|ID|EXPIRE_DATE|CUSTOMER_ID
 1 |2013-12-31 |1
 3 |2013-11-30 |2

With Mysql I would have a query like:
 SELECT ID,min(EXPIRE_DATE),CUSTOMER_ID
 FROM PRODUCT
 GROUP BY CUSTOMER_ID

But I am using HyperSQL and I am not able to obtain this result. 
With HSQLDB I get this error: java.sql. SQLSyntaxErrorException: expression not in aggregate or GROUP BY columns: PRODUCT.ID.  
If in HyperSQL I modify the last row of the query like GROUP BY CUSTOMER_ID,ID I obtain the same entries of the original table.  
How can I compute the product with the soonest expire date for each customer with HSQLDB?? 

A:

Assuming sub queries are supported, you can join the table on itself like such:
SELECT P.ID, P.EXPIRE_DATE, P.CUSTOMER_ID
FROM PRODUCT P
   JOIN (
       SELECT CUSTOMER_ID, MIN(EXPIRE_DATE) MIN_EXPIRE_DATE
       FROM PRODUCT
       GROUP BY CUSTOMER_ID
   ) P2 ON P.CUSTOMER_ID = P2.CUSTOMER_ID
        AND P.EXPIRE_DATE = P2.MIN_EXPIRE_DATE

