Search with group by

Question

Suppose the following table records authors' posts on a blog.

An author may have several posts.

So I have several repetitions of authors in this table of the different posts that he did.

How I should query to find the content of the most recent post according to the data_post column.

For example, the author of id_author = 1 his most recent post is from 25-08-2018 with the content science.

I did the following search but is returning to me the political content instead of science:

All results:

Result with group by: Consultation:

SELECT id_post,id_autor,MAX(data_post),conteudo_post FROM teste group by id_autor order by data_post DESC;

If you want to replicate the example follow create and I played jsfidle json if you want to import the data. https://jsfiddle.net/0ofe976w/1/

CREATE TABLE `teste` (
`id_post` int(11) NOT NULL,
`id_autor` int(11) NOT NULL,
`data_post` date DEFAULT NULL,
`conteudo_post` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id_post`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

Basically, it was not working because it is not possible to join an aggregate column (in the box at the maximum date) with data not grouped.

First, let’s call the main table of T. Then from the subquery, is made a SELECT, returning the maximum date and the corresponding author id, calling it D.

Table D

 id_autor | MAX_DATA
-------------------------
     1    | 25/08/2018
     2    | 25/08/2018
     3    | 25/07/2018
     4    | 27/09/2018

With this new table that has been returned, is made a SELECT with INNER JOIN, pulling the fields where the author id and the date of the tabela D is equal to the author id and date of the tabela T:

SELECT T.id_post, T.id_autor, D.MAX_DATA, T.conteudo_post
FROM (
    SELECT id_autor, MAX(data_post) AS MAX_DATA
    FROM teste
    GROUP BY id_autor
) AS D
INNER JOIN teste AS T
ON T.id_autor = D.id_autor AND T.data_post = D.MAX_DATA;

Reason for Not Functioning

The SELECT simple, simply shows the first data of the non-aggregated column, for example:

SELECT id_autor, MAX(data_post) AS MAX_DATA, conteudo_post FROM teste GROUP BY id_autor;

That one query returns the following order:

| conteudo_autor 
  --------------
|  politica    
|  politica    
|  tecnologia  
|  economia    

That as you can analyze, the image that was placed in the question, each returned content is respectively the first content of each author.