CODEWITHSUNDEEP
sqlpostgresql
S. Costa
S. Costa

Reputation: 17

Group by and grouping functions?

I created a view that have 3 columns: 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.

Upvotes: 0

Views: 37

Answers (4)

Abelisto
Abelisto

Reputation: 15624

If there are several professionals with the same best rating and you want to get all of them, not only the first random:

select t.*
from nota_profissionais t
  join (
    select service_id, max(nota_media) as nota_media 
    from nota_profissionais
    group by service_id) t1 
  on (t.service_id = t1.service_id and t.nota_media = t1.nota_media)

Actually the same result should return the @Hogan's answer if change the ROW_NUMBER() to the RANK() function.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I think the best approach is DISTINCT ON:

SELECT DISTINCT ON (service_id) np.*
FROM nota_profissionais np
ORDER BY service_id, nota_media DESC;

Not only is the shortest in query length, but DISTINCT ON often has better performance in comparison to other methods.

Upvotes: 0

redneb
redneb

Reputation: 23870

You can do it with a LATERAL subquery:

SELECT *
FROM
    (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).

Upvotes: 0

Hogan
Hogan

Reputation: 70523

You can use a windowing function to do this:

SELECT service_id, professional_id, nota_media,
FROM (
  SELECT service_id, professional_id, nota_media,
         ROW_NUMBER() OVER (PARTITION BY service_id ORDER BY nota_media DESC) as RN
) x
WHERE RN = 1

Upvotes: 1

Related Questions