This is a variant of the SQLzoo tutorial.

'world' table contains fields

1. 'population'(assigned to each country),
2. 'name' (all countries) and
3. 'continent' (assigned to each country).

Expected output is a table as shown below

I did try a complicated function as below, but was not able to get it to work due to "SQL error". Not sure why.

SELECT DISTINCT continent
   , (SELECT x.name
      FROM world x 
      WHERE x.population = (SELECT max(y.population) 
                            FROM world y 
                            WHERE x.continent = y.continent)) AS most_populous
   , (SELECT z.name
      FROM world z
      WHERE z.population = (SELECT min(a.population)
                            FROM world a
                            WHERE a.continent=z.continent)) AS least_populous FROM world;     

Is there an easier way to get the required output?

CodePudding user response：

You can try this:

SELECT world.continent,
       (SELECT x.name
          FROM world x
         WHERE x.population = (SELECT max(y.population)
                                 FROM world y
                                WHERE x.continent = y.continent)
           AND world.continent = x.continent
       ) AS most_populous,
      (SELECT z.name
         FROM world z
        WHERE z.population = (SELECT min(a.population)
                                FROM world a
                               WHERE z.continent = a.continent)
          AND world.continent = z.continent
       ) AS least_populous
 FROM world
GROUP BY world.continent;

Thank you

CodePudding user response：

Since I did not had the tables with me, I ended up creating one. It would be better if you could include table and data creation scripts in question. Second, this is going to be a fairly small table so I am not going to worry about performance. I would create a view based on this query and that will be good enough.

Table creation scripts:

USE [StackOverflow]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryPopulation]') AND type in (N'U'))
DROP TABLE [dbo].[CountryPopulation]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CountryPopulation](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NULL,
    [Continent] [nvarchar](max) NULL,
    [Population] [int] NULL,
 CONSTRAINT [PK_CountryPopulation] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [StackOverflow]
GO
SET IDENTITY_INSERT [dbo].[CountryPopulation] ON 
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (1, N'C1', N'Asia', 100)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (2, N'C2', N'Asia', 200)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (3, N'C3', N'Asia', 300)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (4, N'C4', N'Europe', 100)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (5, N'C5', N'Europe', 200)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (6, N'C6', N'Europe', 300)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (7, N'C7', N'Africa', 100)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (8, N'C8', N'Africa', 200)
GO
INSERT [dbo].[CountryPopulation] ([ID], [Name], [Continent], [Population]) VALUES (9, N'C9', N'Africa', 200)
GO
SET IDENTITY_INSERT [dbo].[CountryPopulation] OFF
GO

Query to get result:

SELECT  MinMax.Continent, 
        ForLeast.[name] AS LeastPopulous, 
        ForMost.[name] AS MostPopulous
FROM    CountryPopulation ForMost JOIN 
        CountryPopulation ForLeast JOIN
        (   SELECT  DISTINCT Continent, 
                    MIN([population]) OVER(PARTITION BY continent) AS LeastPopulation,
                    MAX([population]) OVER(PARTITION BY continent) AS MaxPopulation
            FROM    CountryPopulation) MinMax
            
            ON ForLeast.Continent = MinMax.Continent AND ForLeast.[Population] = MinMax.LeastPopulation 
            ON ForMost.Continent = MinMax.Continent AND ForMost.[Population] = MinMax.MaxPopulation

Note the results for Africa. There are 2 rows. It is possible for more than one country to have least or most population. You would want to think on how to handle that scenario.