CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Aaron Broache
Aaron Broache

Reputation: 11

Aggregate first order data to all orders - Microsoft SQL Server

This is for Microsoft SQL Server.

I'm 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:

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

The closest we've got is the following query:

SELECT a.customer_id, MIN(a.purchase_date) As first_occurence
FROM Sales_Transactions_Header as a
GROUP BY a.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.

Essentially all im trying to do is append data from the initial MIN order to the customer ID so that on every order I can also pull in data from their initial order. In this example, that info is the date of their first purchase and their first transaction number.

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

Upvotes: 1

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Use window functions:

SELECT sth.*,
       MIN(purchase_date) OVER (PARTITION BY customer_id) As first_occurence
FROM Sales_Transactions_Header sth;

EDIT:

If you want the first purchase row for each customer, then I would recommend a correlated subquery:

select sth.*
from Sales_Transactions_Header sth
where sth.purchase_date = (select min(sth2.purchase_date)
                           from Sales_Transactions_Header sth2
                           where sth2.customer_id = sth.customer_id
                          );

Upvotes: 2

Related Questions