Select first purchase for each customer

Question

We are trying to select the first purchase for each customer in a table similar to this:

transaction_no  customer_id  operator_id  purchase_date
20503           1            5            2012-08-24
20504           1            7            2013-10-15
20505           2            5            2013-09-05
20506           3            7            2010-09-06
20507           3            7            2012-07-30

The expected result from the query that we are trying to achieve is:

transaction_no  customer_id  operator_id  first_occurence
20503           1            5            2012-08-24
20505           2            5            2013-09-05
20506           3            7            2010-09-06

The closest we've got is the following query:

SELECT customer_id, MIN(purchase_date) As first_occurence
FROM Sales_Transactions_Header
GROUP BY customer_id;

With the following result:

customer_id  first_occurence
1            2012-08-24
2            2013-09-05
3            2010-09-06

But when we select the rest of the needed fields we obviously have to add them to the GROUP BY clause which will make the result from MIN different. We have also tried to joining it on itself, but haven't made any progress.

How do we get the rest of the correlated values without making the aggregate function confused?

Answer 1

You can use the ROW_NUMBER function to help you with that.

This is how to do it for your case.

WITH Occurences AS 
(
    SELECT 
        *,
        ROW_NUMBER () OVER (PARTITION BY customer_id order by purchase_date ) AS "Occurence"            
    FROM Sales_Transactions_Header
)
SELECT 
    transaction_no,
    customer_id,
    operator_id,
    purchase_date
FROM Occurences 
WHERE Occurence = 1