Postgres SQL: Trying to write a query on a 'country' table to find the one with the smallest population in a defined region

[chrisofarabia]

I’m trying to find the country with the smallest population in the region ‘Southern Europe’ from the following Postgres db schema

CREATE TABLE country (
    code character(3) NOT NULL,
    name text NOT NULL,
    continent text NOT NULL,
    region text NOT NULL,
    surfacearea real NOT NULL,
    indepyear smallint,
    population integer NOT NULL,
    lifeexpectancy real,
    gnp numeric(10,2),
    gnpold numeric(10,2),
    localname text NOT NULL,
    governmentform text NOT NULL,
    headofstate text,
    capital integer,
    code2 character(2) NOT NULL,
    CONSTRAINT country_continent_check CHECK ((((((((continent = 'Asia'::text) OR (continent = 'Europe'::text)) OR (continent = 'North America'::text)) OR (continent = 'Africa'::text)) OR (continent = 'Oceania'::text)) OR (continent = 'Antarctica'::text)) OR (continent = 'South America'::text)))
);

I’ve had no problem listing the countries out in population order using this query

SELECT name, continent, region, population FROM country WHERE region = 'Southern Europe' ORDER BY population ASC;

and know that I need to use MIN(population) in there somewhere to get the singular result I want (it’s the Vatican fwiw), but no amount of reconfiguring the query has got me anything other than an error. Can someone point me in the right direction?

There are 15 results for ‘Southern Europe’ out of approx 240 countries total.

[DaveMaxwell]

Use LIMIT

SELECT name
     , continent
     , region
     , population
  FROM country 
 WHERE region = 'Southern Europe' 
 ORDER BY population ASC
 LIMIT 1;

[chrisofarabia]

Thanks Dave,

This course is proving hard work. Yet another evening where I’ve done nothing but “homework”.

[chrisofarabia]

This also gave me the result I needed (eventually), no idea why it wasn’t playing ball earlier

SELECT name 
FROM country 
WHERE population = (
SELECT 
MIN(population) 
FROM country 
WHERE region = 'Southern Europe');

[r937]

this is the correct solution, because it will return ties

the LIMIT 1 approach would not, which is semantically wrong