Search with group by

Question

Search with group by

Navigation

#1 by (2 votes)

1

Suppose the following table that records author posts in a blog.

An author can have several posts.

So I have several reps of authors on this table of the different posts he made.

How do I make a query to fetch the content of the most recent post according to the data_post column.

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

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

All results:

Resultwithgroupby: Inquiry:

SELECTid_post,id_autor,MAX(data_post),conteudo_postFROMtestegroupbyid_autororderbydata_postDESC;

Ifyouwanttoreplicatetheexample,followthecreateandjsfidlejsonifyouwanttoimportthedata. link

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;

sql group-by order-by

asked by anonymous 23.09.2018 / 05:54

1 answer

Change URL of / controller? id = 1 to / controller / 1 Syntax error in 'with' with SQL Server

score 2 · Accepted Answer

Basically, it was not working because it is not possible to merge an aggregated column (in the house to maximum date) with non-clustered data.

First, let's call the main table of T . Then, from subquery , a SELECT is made, returning the maximum date and the corresponding author's 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 was returned, a SELECT is made with INNER JOIN , pulling the fields where the id of the author and the date of tabela D is equal to the id of the author and the date of 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;

Not Working Reason

The% simple%, simply shows the first data of the column not aggregated, for example:

SELECT

This SELECT id_autor, MAX(data_post) AS MAX_DATA, conteudo_post FROM teste GROUP BY id_autor; returns the following order:

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

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