CODEWITHSUNDEEP
phpmysqldatabase
Pushpendra
Pushpendra

Reputation: 4382

Get last record of each month in MySQL....?

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.....

Upvotes: 17

Views: 8490

Answers (4)

Zimzat
Zimzat

Reputation: 654

Building off Dheer's answer:

SELECT 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.

Upvotes: 8

Dheer
Dheer

Reputation: 4066

Assuming that there is only one record for the day;

SELECT * from table where created_on IN (Select MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on) )

Upvotes: 2

reggie
reggie

Reputation: 13721

SELECT * FROM table 
WHERE created_on in 
(select DISTINCT max(created_on) from table 
GROUP BY YEAR(created_on), MONTH(created_on))

Upvotes: 10

halfdan
halfdan

Reputation: 34214

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.

SELECT *, MAX(created_on) FROM table
GROUP BY YEAR(created_on), MONTH(created_on)

Upvotes: 2

Related Questions