CODEWITHSUNDEEP
postgresql
BugsForBreakfast
BugsForBreakfast

Reputation: 815

postgres how to properly use max function

Help please with correct usage of max, I have the following:

select busqueda.valorBusqueda, count(*) from busqueda where usu_id = 24 group by busqueda.valorBusqueda;

and it works, but I want only the max count of it, so far I tried:

select max (busqueda.valorBusqueda, count(*) from busqueda where usu_id = 24 group by busqueda.valorBusqueda);

but no success..

Upvotes: 0

Views: 23

Answers (2)

Ed Bangga
Ed Bangga

Reputation: 13006

you can use subquery.

select max(t.ct) from (
    select busqueda.valorBusqueda, count(*) ct from busqueda 
    where usu_id = 24 group by busqueda.valorBusqueda) t;

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

The easiest solution here is probably to use a LIMIT query:

select valorBusqueda, count(*) as cnt
from busqueda
where usu_id = 24
group by valorBusqueda
order by count(*) desc
limit 1;

Postgres does not support ties with LIMIT, but we can use the RANK analytic function here if you do want all ties for the highest count:

with cte as (
    select valorBusqueda, count(*) as cnt, rank() over (order by count(*) desc) rnk
    from busqueda
    where usu_id = 24
    group by valorBusqueda
)

select valorBusqueda, cnt
from cnt
where rnk = 1;

Upvotes: 2

Related Questions