Skip to main content

Get last record of each month in MySQL…?

Image
By User


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
Labels:

Comments

  1. UserMay 16, 2012 at 10: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 10: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 10: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 10: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 10:37 AM

    did u try ( not tested)

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

    ReplyDelete

Post a Comment

Popular posts from this blog

Why is this Javascript much *slower* than its jQuery equivalent?

By User
I have a HTML list of about 500 items and a "filter" box above it. I started by using jQuery to filter the list when I typed a letter (timing code added later): $('#filter').keyup( function() { var jqStart = (new Date).getTime(); var search = $(this).val().toLowerCase(); var $list = $('ul.ablist > li'); $list.each( function() { if ( $(this).text().toLowerCase().indexOf(search) === -1 ) $(this).hide(); else $(this).show(); } ); console.log('Time: ' + ((new Date).getTime() - jqStart)); } ); However, there was a couple of seconds delay after typing each letter (particularly the first letter). So I thought it may be slightly quicker if I used plain Javascript (I read recently that jQuery's each function is particularly slow). Here's my JS equivalent: document.getElementById('filter').addEventListener( 'keyup', function () { var jsStart = (new Date).getTime()...
3 comments
Read more

Is it possible to have IF statement in an Echo statement in PHP

By User
Thanks in advance. I did look at the other questions/answers that were similar and didn't find exactly what I was looking for. I'm trying to do this, am I on the right path? echo " <div id='tabs-".$match."'> <textarea id='".$match."' name='".$match."'>". if ($COLUMN_NAME === $match) { echo $FIELD_WITH_COLUMN_NAME; } else { } ."</textarea> <script type='text/javascript'> CKEDITOR.replace( '".$match."' ); </script> </div>"; I am getting the following error message in the browser: Parse error: syntax error, unexpected T_IF Please let me know if this is the right way to go about nesting an IF statement inside an echo. Thank you.
5 comments
Read more