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