Monday 3 September 2018

mysql does not check any records within 3 months with a group limit of 1 shows the last transaction

I want to have a mysql query that generates data without records 3 months period. Display the latest transaction group by name limit by 1.
**Sample data (March to May)**

Name    Date of Transaction
Robert     2014-03-03
Angel      2014-02-25
Robert     2014-06-03
Daniel     2014-03-11
Angel      2014-05-31
Christine  2014-01-31
Henry      2014-05-05
Henry      2014-06-01
Nicole     2014-03-25

**It should display:**

Name    Date of Transaction
Daniel     2014-03-11
Nicole     2014-03-25
Angel      2014-05-31
Henry      2014-06-01
Robert     2014-06-03

Is this possible? Here is my query but not working
SELECT *
FROM <tablename>
WHERE date <= CURDATE() - INTERVAL 3 MONTH
GROUP BY name
ORDER BY date
DESC

EDIT
How about without the no records within 3 months?
Name    Date of Transaction
Christine  2014-01-31


When you use group by you can only display columns in your group by clause unless using an aggregate function. Does this help?
SELECT name, max(dtTransaction)
FROM <tablename>
WHERE date <= CURDATE() - INTERVAL 3 MONTH
GROUP BY name

Use max to get the latest date.

0 comments:

Post a Comment