I have a problem in writing a query for MySQL. I have following fields in the DB
id created_on status
1 2011-02-15 12:47:09 1
2 2011-02-24 12:47:09 1
3 2011-02-29 12:47:09 1
4 2011-03-11 12:47:09 1
5 2011-03-15 12:47:09 1
6 2011-03-22 12:47:09 1
7 2011-04-10 12:47:09 1
8 2011-04-11 12:47:09 1
I need to select the last record of each month . That is for the month FEB record # 3 month MARCH record # 6 and for month APRIL record # 8
Please help me.....
Thanks in advance.....
Source: Tips4all, CCNA FINAL EXAM
SELECT * FROM table
ReplyDeleteWHERE created_on in
(select DISTINCT max(created_on) from table
GROUP BY YEAR(created_on), MONTH(created_on))
You first need to group by year and month (otherwise you'd be filtering out months in other years). Use MAX() to get the greatest date for each group.
ReplyDeleteSELECT *, MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on)
Assuming that there is only one record for the day;
ReplyDeleteSELECT * from table where created_on IN (Select MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on) )
Building off Dheer's answer:
ReplyDeleteSELECT r.*
FROM table AS r
JOIN (
SELECT MAX(t.created_on) AS created_on
FROM table AS t
GROUP BY YEAR(t.created_on), MONTH(t.created_on)
) AS x USING (created_on)
Be sure you have indexes on created_on, otherwise this query will kill your database if that table gets more than a couple hundred rows.
did u try ( not tested)
ReplyDeleteSELECT min(id) FROM table GROUP BY MONTH(created_on) WHERE status=1;