Select Distinct is bringing duplicate data Mysql

Question

Select Distinct is bringing duplicate data Mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Hello, I'm having problems with a query, and I need to bring only the last record of each id_face that has in the imagens table, but for some reason it's causing id_faces duplicates

Follow my query:

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 query brings me this:

Notethattheid_face2355repeats,andsoonallothers.Itriedtomakeagroupbyid_facebutthenitgetsmuchworsethedatesaredisplayedinaclutterliketheimagebelow

Cananyonehelpmewiththisquery?followsthestructureofthetablesbelow.

----------------EDITION----------followsanotherquerythatItriedtodousingMAX

selectdistinct(df.id_face),MAX(i.id_imagens)asid_imagens,MAX(date_format(i.dt_cad,'%d/%m/%Y\à\s%H:%i'))ashora_visita,df.nome,df.email,df.cpf,df.celular,date_format(df.data_nasc,'%d/%m/%Y')asidade,CASEWHENdf.idade_api<15THEN'14ou-'WHENdf.idade_apiBETWEEN15AND24THEN'15a24'WHENdf.idade_apiBETWEEN25AND34THEN'25a34'WHENdf.idade_apiBETWEEN35AND44THEN'35a44'WHENdf.idade_apiBETWEEN45AND54THEN'45a54'WHENdf.idade_api>=55THEN'55ou+'ENDasidade_api,CASEWHENdf.genero_api='f'THEN'Feminino'ELSE'Masculino'ENDasgenero,g.nomeasgrupo_nome,loja.nome_lojafromdados_facedfjoinimagensioni.id_face=df.id_faceLEFTJOINgrupogong.id_grupo=df.id_grupoJOINiotoni.id_iot=iot.id_iotJOINlojaoniot.id_loja=loja.id_lojawherei.dt_cadBETWEEN'".$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']."')

mysql query mariadb distinct

asked by anonymous 04.12.2018 / 12:45

2 answers

Concatenation OR, AND, ORDER BY, and RAND () do not work Problems with angular requests

score 0 · Answer 1

The distinct will not bring a line by id, so you will need to define which lines of that id you will bring and filter in the where to come just one line, or, make a cursor. For example, if you only want one row per id with the row with the last visit date, you can use an inner join again with the images table (i1 and i2), where i1.id = i2.id and i1.dt_cad = max (i2.dt_cad), so it will only search for the longest accessed last line.

score 0 · Answer 2

And if you do this:

SELECT 
   **i.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') 
**GROUP BY 
   i.id_face, 
   i.id_imagens, 
   date_format(i.dt_cad, '%d/%m/%Y \à\s %H:%i'), 
   df.nome, 
   df.email, 
   df.cpf,  
   df.celular, 
   date_format(df.data_nasc, '%d/%m/%Y'),    
   l.nome_loja** 
order by i.dt_cad DESC limit 0,24