Firebird Query- Return first row each group

Question

In a firebird database with a table "Sales", I need to select the first sale of all customers. See below a sample that show the table and desired result of query.

---------------------------------------
SALES
---------------------------------------
ID  CUSTOMERID  DTHRSALE
1   25          01/04/16 09:32
2   30          02/04/16 11:22      
3   25          05/04/16 08:10
4   31          07/03/16 10:22
5   22          01/02/16 12:30
6   22          10/01/16 08:45

Result: only first sale, based on sale date.

ID  CUSTOMERID  DTHRSALE
1   25          01/04/16 09:32
2   30          02/04/16 11:22      
4   31          07/03/16 10:22
6   22          10/01/16 08:45

I've already tested following code "Select first row in each GROUP BY group?", but it did not work.

Answer 1

So simple as:

select CUSTOMERID min(DTHRSALE) from SALES group by CUSTOMERID

Answer 2

In Firebird 2.5 you can do this with the following query; this is a minor modification of the second part of the accepted answer of the question you linked to tailored to your schema and requirements:

select   x.id,
         x.customerid, 
         x.dthrsale
    from sales x
    join (select customerid,
                 min(dthrsale) as first_sale
            from sales 
        group by customerid) p on p.customerid = x.customerid
                              and p.first_sale = x.dthrsale
order by x.id

The order by is not necessary, I just added it to make it give the order as shown in your question.

With Firebird 3 you can use the window function ROW_NUMBER which is also described in the linked answer. The linked answer incorrectly said the first solution would work on Firebird 2.1 and higher. I have now edited it.

Answer 3

Search for the sales with no earlier sales:

SELECT S1.*
FROM SALES S1
LEFT JOIN SALES S2 ON S2.CUSTOMERID = S1.CUSTOMERID AND S2.DTHRSALE < S1.DTHRSALE
WHERE S2.ID IS NULL

Define an index over (customerid, dthrsale) to make it fast.