0
I need to bring only the last record of each id_face
that has in the table imagens
, but for some reason it’s bringing id_faces
duplicates.
Follow my consultation:
SELECT
DISTINCT(i.id_face) as id_face,
i.id_imagens as id_imagens,
date_format(i.dt_cad, '%d/%m/%Y \à\s %H:%i') as hora_visita,
df.nome,
df.email,
df.cpf,
df.celular,
date_format(df.data_nasc, '%d/%m/%Y') as idade,
CASE
WHEN df.idade_api < 15 THEN '14 ou -'
WHEN df.idade_api BETWEEN 15 AND 24 THEN '15 a 24'
WHEN df.idade_api BETWEEN 25 AND 34 THEN '25 a 34'
WHEN df.idade_api BETWEEN 35 AND 44 THEN '35 a 44'
WHEN df.idade_api BETWEEN 45 AND 54 THEN '45 a 54'
WHEN df.idade_api >= 55 THEN '55 ou +' END as idade_api,
CASE
WHEN df.genero_api ='f' THEN 'Feminino' ELSE 'Masculino' END as genero,
g.nome as grupo_nome,
l.nome_loja
FROM imagens i
JOIN dados_face df on i.id_face=df.id_face
JOIN loja l on df.id_loja=l.id_loja
LEFT JOIN grupo g on g.id_grupo = df.id_grupo
JOIN iot on iot.id_user='1' WHERE i.dt_cad BETWEEN '2018-09-30 00:00:00' AND '2018-12-04 23:59:59.999999'
AND df.id_grupo like '%'
AND l.id_user='1'
AND i.id_iot in (SELECT id_iot from iot where id_user='1')
order by i.dt_cad DESC limit 0,24
This consultation brings me this:
Note that the id_face
2355
repeats and so does everyone else.
I tried to make a group by id_face
but then it gets much worse, because the dates are displayed as disorderly as in the image below:
Could someone assist me in correcting this query?
Below is the structure of the tables:
Here’s another query I tried using MAX
select
distinct(df.id_face),
MAX(i.id_imagens) as id_imagens,
MAX(date_format(i.dt_cad, '%d/%m/%Y \à\s %H:%i')) as hora_visita,
df.nome,
df.email,
df.cpf,
df.celular,
date_format(df.data_nasc, '%d/%m/%Y') as idade,
CASE WHEN df.idade_api < 15 THEN '14 ou -'
WHEN df.idade_api BETWEEN 15 AND 24 THEN '15 a 24'
WHEN df.idade_api BETWEEN 25 AND 34 THEN '25 a 34'
WHEN df.idade_api BETWEEN 35 AND 44 THEN '35 a 44'
WHEN df.idade_api BETWEEN 45 AND 54 THEN '45 a 54'
WHEN df.idade_api >= 55 THEN '55 ou +'
END as idade_api,
CASE WHEN df.genero_api ='f' THEN 'Feminino' ELSE 'Masculino' END as genero,
g.nome as grupo_nome,
loja.nome_loja
from dados_face df
join imagens i on i.id_face=df.id_face
LEFT JOIN grupo g on g.id_grupo = df.id_grupo
JOIN iot on i.id_iot = iot.id_iot
JOIN loja on iot.id_loja = loja.id_loja
where
i.dt_cad BETWEEN '".$data_incial.":00' AND '".$data_final.":59.999999'
$id_grupo
AND i.id_iot IN (select id_iot from iot where id_user='".$_SESSION['usuario']['id_user']."')
AND df.id_loja IN (select id_loja from loja where id_user='".$_SESSION['usuario']['id_user']."')
I don’t use mysql, but usually
distinct
orgroup by
will group tuples when they are completely equal. In the case of id2355
some information of the two records are different as, for example,id_imagens
andhora_visita
.– Camilo Santos
I guess you had to use MAX().
– Sam
I tried to use max and still not rolled I will show my query with max in the post ( I will edit )
– Jasar Orion
Tried a subselect with MAX ?
– Motta