How do I return all individual rows that meet the criteria of an aggregate function?-CodePudding

For example, let's say that I have a table of purchases, and I want to return a list of all purchases that were made on the same day as the 1st purchase, grouping by individual customers. I don't believe that I can use min(purchase_date), and group by customer, since that will return just one row. How would I go about doing this?

Here's an example. I believe this would return only 1 row, whereas I want to return all orders that fall on the initial purchase date.

select c.name, min(o.purchase_date)
from customers c
join orders o on c.id = o.customer_id
group by c.name

CodePudding user response：

Qualify can come in handy

select c.name, o.purchase_date
from customers c
join orders o on c.id = o.customer_id
qualify o.purchase_date = min(o.purchase_date) over (partition by c.name)

CodePudding user response：

select c.name, min(o.purchase_date)
from(
   select o.customer_id,o.purchase_date, 
          row_number() 
          over(partition by customer_id, date order by id) as firstOrder
     from orders o
)t 
join customers c on c.id = o.customer_id and o.firstOrder = 1

CodePudding user response：

Using QUALIFY and RANK:

SELECT *
FROM customers c
JOIN orders o
 ON c.id = o.customer_id
QUALIFY RANK() OVER(PARTITION BY c.name ORDER BY o.purchase_date) = 1