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
).WHERE rn <= 2
, this will return the first two rows bytransaction-date
for eachcustomer_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 usingSTR_TO_DATE( transaction_date, '%c/%e/%y' )
.'%c/%e/%y'
is MySQL’s notation for'M/d/yy'
.Like so:
this can be simplified to this:
Screenshot proof:
Previous version:
Fiddle