Suppose I had two tables:
Customer - ID | Name | Etc 1 | One | 2 | Two | 3 | Three | 4 | Four | 5 | Five | ... | ... | Sales - Customer ID | Date | Amount 5 | 1/20 | $45 5 | 3/19 | $145 3 | 8/19 | $453 7 | 3/20 | $4513 3 | 9/20 | ... 1 | 3/20 | ... 1 | 1/20 | ...
What I want to do is write a query that will find the first sale for each customer. I am not sure exactly how to do it. I feel like this is group by problem, but the answer is not coming to me.
EDIT: I feel like my first data table did not fully explain my problem. (Honestly, I didn’t even realize this facet of my problem until, I was coding the solution) Note: There is more than one customer per sale.
Sales - Sale ID | Customer ID | Date | Amount 1 | 5 | 1/20 | $45 5 | 5 | 3/19 | $145 8 | 3 | 8/19 | $453 7 | 7 | 3/20 | $4513 3 | 4 | 9/20 | ... 2 | 1 | 3/20 | ... 1 | 1 | 1/20 | ...
Advertisement
Answer
You can use a subquery which assigns a row number to each sale for each customer, ordering by ascending date, and then select only the first rows:
SELECT "Customer ID", "Date", "Amount" FROM ( SELECT "Customer ID", "Date", "Amount", ROW_NUMBER() OVER (PARTITION BY "Customer ID" ORDER BY "Date") AS rn FROM Sales) s WHERE rn = 1