MySQL return only the first transaction between dates

Question

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?

Not sure what you want here... i assume you want to select the records `1 - abc - 2018-10-04 17:34:24` and `2 - xyz - 2018-11-09 17:34:24` ? Because those are the "first" records of the clients.. — Raymond Nijland, Dec 11 '18 at 18:13
Hi Raymond thank you for your reply, yes that's right, I would like to get only the first records — Dimitrios, Dec 11 '18 at 18:16
That duplication link wil not work in this case @Barmar it will not handle ties — Raymond Nijland, Dec 11 '18 at 18:21
@RaymondNijland You can first minimize the date then minimize the ID within that. — Barmar, Dec 11 '18 at 18:27
@Barmar I'm going through the post and I'm not sure I understand what how should I minimise the date value, would appreciate if you could give me a hint on the query, thank you in advance — Dimitrios, Dec 11 '18 at 18:39
Not sure how I can give you just a hint. I took pity and wrote the query in an answer, even though it's just like the queries in the other question. — Barmar, Dec 11 '18 at 18:42

score 2 · Accepted Answer · answered Dec 11 '18 at 18:42

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

MySQL return only the first transaction between dates

1 Answers1