0

Help!

How do I find the first sku on the first order for a customer? Assume I have a table with the following columns and am using MySQL:

  • order_number
  • email
  • sku

For example, I know we can do the following to find the first order number:

select   email, min(order_number) 
from     orders
group by email

If I run the following query, it returns a seemingly random sku from the customers order history:

select   email, min(order_number), sku 
from     orders
group by email

I tried running a subquery, but no luck there either:

select   email, min(order_number), 
             (select sku from orders o2 
              where o2.email = o1.email 
              and o2.order_number = o1.order_number)
from     orders o1
group by email

How can I do this?

Thanks!

Ben
  • 33
  • 5
  • 1
    You should be able to adapt either duplicate to your needs quite readily. If not, please ask another question explaining the issues you are having doing so. – Nick Mar 17 '20 at 04:29
  • select sku, email,order_number from ( select sku, email,order_number, rank() OVER( partition by email order by order_number) rn from orders )rs where rs.rn=1 – Koushik Roy Mar 17 '20 at 04:35

0 Answers0