Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cities (
  city_id serial PRIMARY KEY
, city text
, country text
, population int
);

INSERT INTO cities (city, country, population)
VALUES
  ('a1', 'a', 1)
, ('a2', 'a', 3)
, ('a3', 'a', 3)
, ('b1', 'b', 1)
, ('b3', 'b', 3)
, ('b2', 'b', 3)
, ('c1', 'c', 1)  -- c has only 1 city
;

CREATE TABLE



INSERT 0 7


-- create aggregate, since it's missing here on dbfiddle:

CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement)
  RETURNS anyelement
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $2';

CREATE AGGREGATE public.last(anyelement) (
  SFUNC = public.last_agg
, STYPE = anyelement
, PARALLEL = safe
);

CREATE FUNCTION



CREATE AGGREGATE


SELECT country
     , count(*) AS ct_cities
     , max(population) AS highest_population
     , last(city ORDER BY population, city) AS biggest_city
--   , first(city ORDER BY population DESC NULLS LAST, city DESC NULLS LAST) AS biggest_city2
FROM   cities
GROUP  BY country
HAVING count(*) > 1;


country
ct_cities
highest_population
biggest_city


a
3
3
a3

b
3
3
b3



SELECT 2


SELECT DISTINCT ON (country)
       country
     , count(*) OVER (PARTITION BY country) AS ct_cities
     , population AS highest_population
     , city AS biggest_city
FROM   cities c
WHERE  EXISTS (SELECT FROM cities c1 WHERE c1.country = c.country AND c1.ctid <> c.ctid)
ORDER  BY country, population DESC NULLS LAST, city DESC NULLS LAST;


country
ct_cities
highest_population
biggest_city


a
3
3
a3

b
3
3
b3



SELECT 2


SELECT *
FROM  (
   SELECT DISTINCT ON (country)
          country
        , count(*) OVER (PARTITION BY country) AS ct_cities
        , population AS highest_population
        , city AS biggest_city
   FROM   cities c
   ORDER  BY country, population DESC NULLS LAST, city DESC NULLS LAST
   ) sub
WHERE  ct_cities > 1;


country
ct_cities
highest_population
biggest_city


a
3
3
a3

b
3
3
b3



SELECT 2