Wednesday, May 16, 2012

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


Source: Tips4all
Post by On
Tags , , ,

5 comments:

  1. UserMay 16, 2012 at 3:37 AM

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

    ReplyDelete
  2. UserMay 16, 2012 at 3:37 AM

    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)

    ReplyDelete
  3. UserMay 16, 2012 at 3:37 AM

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

    ReplyDelete
  4. UserMay 16, 2012 at 3:37 AM

    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.

    ReplyDelete
  5. UserMay 16, 2012 at 3:37 AM

    did u try ( not tested)

    SELECT min(id) FROM table GROUP BY MONTH(created_on) WHERE status=1;

    ReplyDelete

Subscribe to: Post Comments (Atom)