CODEWITHSUNDEEP
mysql
Dimitrios
Dimitrios

Reputation: 47

MySQL return only the first transaction between dates

I have a table to keep every transaction from clients,

is it possible to retrieve only the first transaction a client did between 2018-10-01 and 2018-12-31

Example:

+----+------+---------------------+
| id | name |    transactions     |
+----+------+---------------------+
|  1 | abc  | 2018-10-04 17:34:24 |
|  2 | xyz  | 2018-11-09 17:34:24 |
|  3 | xyz  | 2018-12-05 17:34:24 |
|  4 | abc  | 2018-12-11 17:34:24 |
+----+------+---------------------+

I would like to get only:

abc - 2018-10-04 17:34:24
xyz - 2018-11-09 17:34:24

is that posible?

Upvotes: 1

Views: 624

Answers (1)

Barmar
Barmar

Reputation: 780808

Follow the patterns in SQL select only rows with max value on a column except use MIN() instead of MAX(). And add a BETWEEN condition to restrict the time period.

SELECT t1.*
FROM yourTable AS t1
JOIN (SELECT name, MIN(transactions) AS minTransactions
      FROM yourTable
      WHERE transactions BETWEEN '2018-10-01' and '2018-12-31 23:59:59'
      GROUP BY name) AS t2 ON t1.name = t2.name AND t1.transactions = t2.minTransactions

Upvotes: 3

Related Questions