How to combine max and count in one query

Question

I have a simple question about 'max' and 'count' functions.

I read some posts already but they don't satisfy me or i cannot understand them well enough to use them

I tried to refer to this

Here is my query:

select wojewodztwo, count(*) as liczba from wojewodztwa 

inner join powiaty on wojewodztwa.klwoj = powiaty.klwoj 
inner join gminy on powiaty.klpow = gminy.klpow 
inner join miejscowosci on gminy.klgm = miejscowosci.klgm 

where miejscowosc = 'Nowy Dwór' 
group by wojewodztwo order by count(*) desc

And the result of this query looks like this

Now i want to get only max value rows as the answer. I don't want to use Limit 2 I thought of doing something like max(count) but it seems that aggregate functions cannot be stacked on one of eachother

Answer 1

Using a windowed function and a CTE will be quicker and more elegant:

WITH
T AS
(
SELECT wojewodztwo, COUNT(*) as liczba, MAX(COUNT(*)) OVER() AS MAX_liczba  
FROM   wojewodztwa 
       INNER JOIN powiaty 
          ON wojewodztwa.klwoj = powiaty.klwoj 
       INNER JOIN gminy 
          ON powiaty.klpow = gminy.klpow 
       INNER JOIN miejscowosci 
          ON gminy.klgm = miejscowosci.klgm 
WHERE  miejscowosc = 'Nowy Dwór' 
GROUP  BY wojewodztwo
)
SELECT wojewodztwo, liczba
FROM   T
WHERE  liczba = MAX_liczba;

Answer 2

It sounds like you want to put FETCH FIRST 1 ROW WITH TIES at the end of your query. This WITH TIES feature was introduced in v13.

Before that, I think you would need to wrap another query using window functions around your existing query (or do it on the client side). To use the window function, you would actually have to wrap it in two layers of select, as you can't use window functions in a where clause, so you need to define it on one level and use it on the outer level.

So it would look something like this:

select wojewodztwo, liczba from ( 
  select wojewodztwo, liczba, max(liczba) over () as _max from (   
    select wojewodztwo, count(*) as liczba from wojewodztwa 
    inner join powiaty on wojewodztwa.klwoj = powiaty.klwoj 
    inner join gminy on powiaty.klpow = gminy.klpow 
    inner join miejscowosci on gminy.klgm = miejscowosci.klgm 
    where miejscowosc = 'Nowy Dwór' 
    group by wojewodztwo
  ) f
) ff where _max=liczba