S. Costa S. Costa - 1 year ago 61
SQL Question

Group by and grouping functions?

I created a view that have 3 collumns: service_id, professional_id and avg(rating), and this view is working fine.

CREATE VIEW nota_profissionais AS
SELECT service_id, professional_id, avg(nota) AS nota_media
FROM avaliacao, servico_executado, solicitacao
WHERE avaliacao.service_id = servico_executado.id
AND servico_executado.id_solicitacao = solicitacao.id
GROUP BY service_id, professional_id;

Now, for each service_id it returns (each type of service), I need to show the professional which has the best average rating. I tried doing this:

SELECT service_id, professional_id, max(nota_media) AS nota_media
FROM nota_profissionais GROUP BY service_id, professional_id;

But it returns the exact same thing from the view, showing every professional_id per service_id. I know why that's happening, but don't know to how to do this query correctly. Sorry if I'm not being clear, as english isn't my native language.

Answer Source

You can do it with a LATERAL subquery:

    (SELECT DISTINCT service_id FROM nota_profissionais) t1,
    LATERAL (SELECT professional_id, nota_media
             FROM nota_profissionais
             WHERE service_id=t1.service_id
             ORDER BY nota_media DESC LIMIT 1) t2

This will find all distinct service_ids (first subquery) and then for each one of them, it will find a the best professional (second subquery, the one with LATERAL).

If you have a lot of data, make sure that you have an index on (service_id, nota_media DESC).