skip to Main Content

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:

customer_name transaction_date
Jim 1/1/22
Jim 3/1/22
Pam 1/2/22
Dwight 12/23/21
Pam 4/1/20
Jim 3/3/22
Dwight 1/1/22
Pam 8/1/22
Dwight 10/1/22

I would like it this way:

customer_name transaction_date
Dwight 12/23/21
Dwight 1/1/22
Jim 1/1/22
Jim 3/1/22
Pam 4/1/20
Pam 1/2/22

2

Answers


    • 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 the very silly American date format you can’t sort by it directly – you need to first parse it using STR_TO_DATE( transaction_date, '%c/%e/%y' ).

      • '%c/%e/%y' is MySQL’s notation for 'M/d/yy'.

    Like so:

    SET sql_mode = 'ANSI_QUOTES'; /* No way am I using backticks... */
    
    WITH withParsedDate AS (
        SELECT
            customer_name,
            STR_TO_DATE( transaction_date, '%c/%e/%y' ) AS "date"
        FROM
            Customer
    ),
    withRowNumber AS (
        SELECT
            customer_name,
            "date",
            ROW_NUMBER() OVER ( PARTITION BY customer_name ORDER BY "date" ASC ) AS rn
        FROM
            withParsedDate
    )
    SELECT
        customer_name,
        "date",
        rn
    FROM
        withRowNumber
    WHERE
        rn <= 2;
    

    this can be simplified to this:

    SET sql_mode = 'ANSI_QUOTES'; /* No way am I using backticks... */
    
    SELECT
        t2.customer_name,
        t2."date",
        t2.rn
    FROM
        (
            SELECT
                c.customer_name,
                STR_TO_DATE( c.transaction_date, '%c/%e/%y' ) AS "date",
                ROW_NUMBER() OVER ( PARTITION BY c.customer_name ORDER BY STR_TO_DATE( c.transaction_date, '%c/%e/%y' ) ASC ) AS rn
            FROM
                Customer AS c
        ) AS t2 
    WHERE
        t2.rn <= 2;
    

    Screenshot proof:

    enter image description here

    Previous version:

    enter image description here

    Login or Signup to reply.
  1. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search