Essentially, I am trying to group customer names and then sort by transaction date in ASC order and then only get the first two transactions (LIMIT 2)

SELECT 
    customer_names,
    transaction_date
FROM
    Customer
ORDER BY
    customer_names,
    transaction_date
LIMIT
    4

What would be this table w/o the ORDER BY and the LIMIT:

I would like it this way:

CodePudding user response：

• You can ignore SQL's GROUP BY clause for this problem.

  • GROUP BY doesn't actually bunch rows into groups comprised of rows, instead think of it as the "AGGREGATE BY" clause instead.

• Instead, use first use ROW_NUMBER() OVER ( PARTITION BY customer_name ORDER BY transaction_date ASC ) to assign a relative ordinal to each row (rn).

  • Then filter by that such that WHERE rn <= 2, this will return the first two rows by transaction-date for each customer_name.

• As your transaction_date is a string using

  Previous version:

  

  CodePudding user response：

  select  customer_name   
         ,str_to_date(transaction_date, '%m/%d/%Y') as transaction_date
  from    (
           select *
                 ,row_number() over(partition by customer_name order by str_to_date(transaction_date, '%m/%d/%Y')) as rn
           from   t
          ) t
  where   rn <= 2
    
  

  customer_name	transaction_date
  Dwight	2021-12-23 00:00:00
  Dwight	2022-01-01 00:00:00
  Jim	2022-01-01 00:00:00
  Jim	2022-03-01 00:00:00
  Pam	2020-04-01 00:00:00
  Pam	2022-01-02 00:00:00

  Fiddle