View grouped data

Question

View grouped data

Asked 9 years, 7 months ago

Viewed 289 times

0

I am making a query in the database and I am having difficulties with data duplications, so far, this is my query:

SELECT * FROM tec_postagens tp
INNER JOIN tec_historico_status th ON tp.protocolo=th.protocolo
WHERE tp.protocolo = '1168'
ORDER BY tp.protocolo DESC, tp.datahora DESC

the result is :

inserir a descrição da imagem aqui

the first table is tec_postagens, the second is tec_historico_status, I need to show all posts along with the data from tec_historico_status both have the same protocolo what differentiates is the datahora I need to show by order of datahora

thank you.

2

In my view it is not wrong. Because you are linking everything in a single query. These duplicities must be adjusted using the programming language in their application.

– Antony Alkmim

2014/11/12 at 13:05
You just don’t want duplicate data to appear? Can’t use DISTINCT?

– Rene Sá

2014/11/12 at 13:08
never used DISTINCT @Renesá , could help me?

– Furlan

2014/11/12 at 13:16
1

Thus: SELECT DISTINCT * FROM tec_postagens tp
INNER JOIN tec_historico_status th ON tp.protocolo=th.protocolo
WHERE tp.protocolo = '1168'
ORDER BY tp.protocolo DESC, tp.datahora DESC With DISTINCT, if there are multiple identical results, evaluating the sequence by ORDER BY, it returns only one. @Rene, put the solution as answer.

– Gustavo Cinque

2014/11/12 at 13:22
was the same result @Gustavocinque

– Furlan

2014/11/12 at 13:46
Furlan where there’s repetition, I don’t understand...

– Jorge B.

2014/11/12 at 14:08
in the column posting @Jorgeb.

– Furlan

2014/11/12 at 15:24
Try to explain your problem better in the question. It is not possible to realize what you want.

– Jorge B.

2014/11/12 at 15:33

Show 3 more comments

2 answers

3

If you don’t want repetition in the column postagem has to make a GROUP BY of that column:

SELECT * FROM tec_postagens tp
INNER JOIN tec_historico_status th ON tp.protocolo=th.protocolo
WHERE tp.protocolo = '1168'
ORDER BY tp.protocolo DESC, tp.datahora DESC
GROUP BY tp.postagem

[EDIT]

If it’s only to order by datahora of the second table just change

ORDER BY tp.protocolo DESC, tp.datahora DESC

for

ORDER BY tp.protocolo DESC, th.datahora DESC

Notice that I’ve changed tp.datahora for th.datahora

right @Jorge but now I miss some 'changes' he loses because of GROUP

– Furlan

2014/11/12 at 15:48
@Furlan Which of the lines in the image you want NOT to appear?

– Jorge B.

2014/11/12 at 16:02
repeated posts cannot appear, and cannot lose the changes

– Furlan

2014/11/12 at 16:11
Would you take a whole row from there? Tell me the number of the line you want not to appear from that table in the image

– Jorge B.

2014/11/12 at 16:15
do not have to take anything, I want to show only the posts and changes according to the time of each one, ie listing them ordered by the column 'date time'

– Furlan

2014/11/12 at 16:19
Look at my edition... that’s it?

– Jorge B.

2014/11/12 at 16:25
Valew guys for help, I managed to solve my mistake, I changed my structure, thank you all, Valew @Jorge

– Furlan

2014/11/12 at 16:44
1

@Jorgeb. Great example +1

– Guilherme Nascimento

2014/12/23 at 14:53

Show 3 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.

by Rene Sá • **823** points · Answer 1 · 2014-11-12T14:31:43+00:00

Try it this way, using DISTINCT.

SELECT DISTINCT * FROM tec_postagens tp 
INNER JOIN tec_historico_status th ON tp.protocolo=th.protocolo 
WHERE tp.protocolo = '1168' 
ORDER BY tp.protocolo DESC, tp.datahora DESC

If that doesn’t work, try changing the * by the names of the columns.