I need the GROUP BY clause read after HAVING

Question

I need the GROUP BY clause read after HAVING

Asked 4 years, 8 months ago

Viewed 87 times

0

My problem is the following, the distance is the crucial information for my research, and I need to use also GROUP by not to appear several equal products!

Then as the group by comes before the having, IF the first "p.id" information returned is at some distance greater than 5(even existing that product in other establishment it will not return to me) pq the group by ordered for the first appearance of p.id and having came after and took out several establishment with distance >5, therefore taking out various products that would appear,

Someone’s been through something like this and has a solution?

I have the following mysql SQL:

SELECT p.id, p.link_imagem, p.nome produto, p.descricao, p.volume, p.volume_tipo, t.tipo, e.nome,
                            ROUND((6371 * acos(
                                    cos(radians(end.latitude)) *
                                    cos(radians(e.latitude)) *
                                    cos(radians(end.longitude) - radians(e.longitude)) +
                                    sin(radians(end.latitude)) *
                                    sin(radians(e.latitude))
                                )),1) AS distancia
                          FROM produtos as p
                INNER JOIN enderecos as end on(end.usuarioid = '94' and end.status = '1')               
                INNER JOIN cardapios as c on(c.produtoid = p.id)
                INNER JOIN estabelecimentos as e on(e.id = c.estabelecimentoid)
                INNER JOIN tipos as t on(t.id = p.tipoid)
                WHERE p.nome like '%coca%' 
                GROUP BY p.id
                HAVING distancia <= 5 ORDER BY distancia

HAVING always comes after GROUP, is a filter over aggregated values like SUM(), COUNT(), which you are not even using. Without some rows of tables there is no way to test, but I think you should not use GROUP BY and HAVING should be replaced by WHERE. INNER JOIN with the addresses is a Cartesian product, so you will obviously have multiple lines with the same product because you are measuring the distance of each product against a list of addresses.

– epx

2020/03/11 at 03:30
You can do select t.* from (sua_consulta_sem_group_by_sem_having) as t where t.distancia <= 5 group by t.id order by t.distancia, recalling that this will reduce the performance of the query, which may be imperceptible or not.

– Benilson

2020/03/11 at 10:37

1 answer

Browser other questions tagged mysql sql query

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

by Heitor Scalabrini • **781** points · Answer 1 · 2020-03-11T11:07:17+00:00

If you want to perform one command before the other in it SELECT I would recommend using the concept of SUBSELECT:

--Segundo SELECT
SELECT DISTINCT resultado.*
FROM (
      --Primeiro SELECT
      SELECT p.id, p.link_imagem, p.nome produto, p.descricao, p.volume, 
      p.volume_tipo, t.tipo, e.nome,
      ROUND((6371 * acos(
              cos(radians(end.latitude)) *
              cos(radians(e.latitude)) *
              cos(radians(end.longitude) - radians(e.longitude)) +
              sin(radians(end.latitude)) *
              sin(radians(e.latitude))
          )),1) AS distancia
      FROM produtos as p
      INNER JOIN enderecos as end on(end.usuarioid = '94' and end.status = '1')               
      INNER JOIN cardapios as c on(c.produtoid = p.id)
      INNER JOIN estabelecimentos as e on(e.id = c.estabelecimentoid)
      INNER JOIN tipos as t on(t.id = p.tipoid)
      WHERE p.nome like '%coca%' 
      --Primeiro o HAVING
      HAVING distancia <= 5 ORDER BY distancia  
      ) resultado
--Segundo o Group BY
GROUP BY resultado.id

First is executed the first SELECT with the HAVING, then runs a SELECT with the GROUP BY, also includes the DISTINCT to ensure the removal of duplicate data.