SQL how to select countries that appear every year?-CodePudding

I have a datatable with a column "year" and a column "country". Each country can appear more than one year, ranging from 1900 to 2021. How to select only countries that appear in all the year (1900 to 2021)?

This is my code in selecting which is the earliest and latest year:

SELECT
    MIN(year) AS EarliestYear,  
    MAX(year) AS LatestYear
FROM 
    owid_energy_data;

CodePudding user response：

Group by country and take the count of rows. Find out if the count of rows match the number of years in the range (2021 - 1900 1)

SELECT country, count(year) from owid_energy_data
GROUP BY country HAVING count(year)= 2021 - 1900   1 ;

CodePudding user response：

Generalizing off of Extreme_Tough's answer:

SELECT sub.country
  FROM (SELECT DISTINCT country, year
          FROM owid_energy_data) sub
 GROUP BY country
HAVING COUNT(year) = MAX(year) - MIN(year)   1

The purpose of DISTINCT is to prevent countries will be found as occuring for every year in case they appear multiple times for the lowest or the highest year, but are missing for some year between.