Postgres using MAX operator

Question

I have a schema , where u have a Travel table, People table and a ClientTravel table that references both the Travel table and People Table. I want to do the query that gives me the Client(s) that made the most travels in a year.

I've done a subquery that gives me the count of times a ClientId appeared in the ClientTravel Table like so:

SELECT cv.idpessoa , count(cv.idpessoa) as noviagens 
FROM viagem v , clienteviagem cv ,pessoa p 
WHERE EXTRACT(YEAR FROM v.dtviagem)=2021 and v.idsistema = cv.viagem and p.id =cv.idpessoa 
GROUP BY cv.idpessoa

i Would like to do another query that uses the result of the previous one where it only gives me the PeopleId with the max Count.

I've managed to do a table that looks like this:

idpessoa	count
1234	10
5431	1
1242	3
8567	5

And now i want to retrieve a table like this :

idpessoa
1234

Find the idpessoa that has the max value of count. Thanks for your time

score 2 · Accepted Answer · 2022-11-22 16:46:47Z

2

You can order by the count, then limit the result to one row:

SELECT cv.idpessoa , count(cv.idpessoa) as noviagens 
FROM viagem v 
  JOIN clienteviagem cv ON v.idsistema = cv.viagem
  JOIN pessoa p ON p.id = cv.idpessoa 
WHERE EXTRACT(YEAR FROM v.dtviagem) = 2021
GROUP BY cv.idpessoa
ORDER BY count(cv.idpessoa) desc
LIMIT 1

If you have multiple rows with the same highest count, this will only return one of them. If you want to see all of them you can use:

fetch first 1 row with ties

instead of limit 1

This can be done using the max() aggregate, but it means you don't get to see the other column from the result:

select max(noviagens)
from (
  SELECT cv.idpessoa , count(cv.idpessoa) as noviagens 
  FROM viagem v 
    JOIN clienteviagem cv ON v.idsistema = cv.viagem
    JOIN pessoa p ON p.id = cv.idpessoa 
  WHERE EXTRACT(YEAR FROM v.dtviagem) = 2021
  GROUP BY cv.idpessoa
) t

Another option is to use max() as a window function:

select cv.idpessoa, noviagens
from (
  SELECT cv.idpessoa, 
         count(cv.idpessoa) as noviagens,
         max(count(cv.idpessoa)) over () as max_noviagens
  FROM viagem v 
    JOIN clienteviagem cv ON v.idsistema = cv.viagem
    JOIN pessoa p ON p.id = cv.idpessoa 
  WHERE EXTRACT(YEAR FROM v.dtviagem) = 2021
  GROUP BY cv.idpessoa
) t
where noviagens = max_noviagens;

edited Nov 22, 2022 at 16:46

answered Nov 22, 2022 at 16:32

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Kripthonite Over a year ago

But do u think theres a way u can work with max here?

Collectives™ on Stack Overflow

Postgres using MAX operator

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related