Take the lowest values of a Count using group by

Asked

Viewed 143 times

-1

Cheers guys, I have the following problem... The query below is almost perfect, but it is generating repeated Ids, what I need is the lowest Count value of each ID.

inserir a descrição da imagem aqui

The ideal result would be:

ID  |  COUNT
1      1
2      0
3      11
4      0
5      0

Anyone can help?

sql postgresql query group-by

1 answer

1


You can use the clause WITH to reuse its query and bring only the greatest result of COUNT:

WITH quantidade AS (
  [SUA QUERY]
)
SELECT q1.*
  FROM quantidade q1
 WHERE NOT EXISTS(SELECT 1
                    FROM quantidade q2
                   WHERE q2.id = q1.id
                     AND q2.count < q1.count)

SELECT in WITH

The basic value of SELECT in WITH is to break down Complicated queries into Simpler Parts.

In free translation:

The basic goal of SELECT within WITH is to break complicated quries into simpler parts.

  • Buddy, I can’t thank you enough! I was a long time breaking my head with this... by the way I didn’t even know this command with, anyway, thank you :D

Browser other questions tagged sql postgresql query group-by

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