Mysql – Grouping by one column and sorting by date

• 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:

Previous version: