17

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

Pushpendra
  • 4,344
  • 5
  • 36
  • 64
  • 2
    possible duplicate of [can i use aggregation function (LAST) in mysql??](http://stackoverflow.com/questions/5495913/can-i-use-aggregation-function-last-in-mysql) – Shakti Singh Apr 11 '11 at 13:23
  • Check the answer that I have posted. I believe it will answer your question. – reggie Apr 11 '11 at 13:38
  • see accepted answer in http://stackoverflow.com/questions/1379565/mysql-first-and-last-record-of-a-grouped-record-aggregate-functions for a more efficient solution – o17t H1H' S'k Jun 25 '12 at 12:45

4 Answers4

10
SELECT * FROM table 
WHERE created_on in 
(select DISTINCT max(created_on) from table 
GROUP BY YEAR(created_on), MONTH(created_on))
reggie
  • 13,313
  • 13
  • 41
  • 57
  • 1
    This should be re-written to put the sub-query in a JOIN, otherwise this will destroy your database performance as the table grows: http://www.mysqlperformanceblog.com/2010/10/25/mysql-limitations-part-3-subqueries/ – Zimzat Apr 11 '11 at 14:34
8

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.

Zimzat
  • 654
  • 3
  • 14
  • subquery takes more than 10 min for me... I have approx. 28 entries per day.. Joins query have much improvement.. thanks its working for big tables.. – arulraj.net Sep 18 '12 at 07:29
  • Is there any way to swap USING for ON? The framework I'm using only seems to use ON but I get a different umber of results – Robert Went Dec 04 '15 at 14:57
  • @RobertWent Yes: http://stackoverflow.com/a/11367066/175303 USING() is just shortform for ON() so the former can always be expanded to the latter. – Zimzat Dec 06 '15 at 18:48
  • 1
    what if I have multiple records with the exact same created time? I'd need the join to only match the first record with the same timestamp – ndru Jul 13 '18 at 16:01
2

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)
halfdan
  • 33,545
  • 8
  • 78
  • 87
  • 1
    This, like the last two answers, won't return the expected results. When grouping on results MySQL will not return the expected row results for fields that are not in the grouping. In other words, non-grouped fields are chaotic if they match more than one row. – Zimzat Apr 11 '11 at 13:21
  • http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same. – Zimzat Apr 11 '11 at 13:52
2

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) )
Dheer
  • 3,926
  • 6
  • 34
  • 45
  • This will work even if there is more than one record per day. The only time this will fail is if there is more than one record per second, specifically the last second of the month. Adding one more LEFT JOIN to the same table to select based on the highest ID will be the solution to this problem. – Zimzat Apr 11 '11 at 13:38
  • Oh, I might also add that using a sub-query in an 'IN' isn't recommended, as it kills MySQL performance. The database will run the query for every row in the master table. It would be better to move it into a JOIN sub-query. – Zimzat Apr 11 '11 at 13:40