Pick up the most recent month's records

1

I have a simple table that stores titles and date of publication, this in DATE format, and I need to get all the records of the most recent month / year.

select titulo, publicacao FROM teste1 ORDER BY publicacao DESC
    
asked by anonymous 30.08.2014 / 00:18

2 answers

5

Given the most recent month / year within the table, you can use a variable:

SET @ultimaData := (SELECT publicacao FROM teste1 ORDER BY publicacao DESC LIMIT 1);

SELECT titulo, publicacao FROM teste1 
WHERE YEAR(publicacao) = YEAR(@ultimaData) 
AND MONTH(publicacao) =  MONTH(@ultimaData);

If you want the most recent month / year considering today, you can replace the variable with now()

SELECT titulo, publicacao FROM teste1 
WHERE YEAR(publicacao) = YEAR(now()) 
AND MONTH(publicacao) =  MONTH(now());

Example in SQLFiddle

    
30.08.2014 / 00:48
3

If you want to find all records where the field in question has the same month and year as the current date, this is equivalent to looking for values greater than or equal to the first day of the current month.

We can do this like this:

SELECT
  titulo
, publicacao
FROM teste1
WHERE publicacao >= DATE_SUB( DATE( NOW() ), INTERVAL DAY( NOW() ) -1 DAY )
ORDER BY
  publicacao DESC

In spite of the apparent complexity, if the given field is indexed, this filter will be more efficient than one done for month and year separately, after all it will benefit from the index.

Now explaining in parts.

The expression

DATE_SUB( NOW(), INTERVAL x DAY )

subtract x days of current day.

To get the first day of the month we should subtract from the current date the number of the current day minus 1. For example: 28/08/2014 - 27 = 01/08/2014

In this way

DATE_SUB( NOW(), INTERVAL DAY( NOW() ) -1 DAY )

returns the first day of the month.

But this still does not fit the filter we want, in case the field in question is a DATETIME, because the current time can be greater than the time of a record of the first day by changing the result we want from the selection.

To avoid this problem we convert NOW () to DATE and arrive at the expression I presented.

DATE_SUB( DATE( NOW() ), INTERVAL DAY( NOW() ) -1 DAY )
    
30.08.2014 / 03:38