MIN MAX using CASE statement in SQL Server

Question

I have a table like this. All I want in country Name, Population number and if the population in MAX then label MAX and similarly for MIN. I have written below query in SQL Server using the CASE statement.

/* MIN/MAX Population amongst the countries */
SELECT 
    country, population,
    CASE
        WHEN population == MIN(population) THEN "MIN"
        WHEN population == MAX(population) THEN "MAX" ELSE "NA"
    END as "pop_stats"
FROM
    countries_by_population;

You should give us your sample data with the corresponding result as a text instead of an image — Nguyễn Văn Phong, Feb 09 '20 at 01:42

Nguyễn Văn Phong · Answer 1 · 2020-02-09T02:43:55.853

Demo on db<>fiddle

Firstly, You should store @Min_Population and @Max_Population by getting like below

DECLARE @Min_Population decimal(18,2), @Max_Population decimal(18,2)
SELECT @Min_Population = MIN(population), @Max_Population = MAX(population)
FROM countries_by_population

After that, you can compare the value for each item like (Note that character in SQL is closed in single quote instead of double one)

CASE
    WHEN population = @Min_Population THEN 'MIN'
    WHEN population = @Max_Population THEN 'MAX' ELSE 'NA'
END as pop_stats

Full query

DECLARE @Min_Population decimal(18,2), @Max_Population decimal(18,2)
SELECT @Min_Population = MIN(population), @Max_Population = MAX(population)
FROM countries_by_population

SELECT country, population,
    CASE
        WHEN population = @Min_Population THEN 'MIN'
        WHEN population = @Max_Population THEN 'MAX' ELSE 'NA'
    END as pop_stats
FROM countries_by_population;

Does this answer your question? Let me know if you need any help @rushikesh maheshwari — Nguyễn Văn Phong, Feb 09 '20 at 02:01

pwilcox · Answer 2 · 2020-02-09T05:21:27.907

0

Use over() to make it a windowed function with no partitioning:

SELECT country, population,
    CASE
        WHEN population = MIN(population) over() THEN 'MIN'
        WHEN population = MAX(population) over() THEN 'MAX' 
        ELSE 'NA'
    END as pop_stats
FROM countries_by_population;

edited Feb 09 '20 at 05:21

answered Feb 09 '20 at 01:28

pwilcox

5,542
1
19
31

3

Your syntax has too many things incorrect. ex: **==** , **"MIN"** (double quotes), etc. Please check again – Nguyễn Văn Phong Feb 09 '20 at 02:00
@Phong. Corrected the '==' and the double quotes. Thanks. – pwilcox Feb 09 '20 at 05:22

Barbaros Özhan · Answer 3 · 2020-02-09T05:30:22.540

You can use a window analytic function such as DENSE_RANK() including ORDER BY population clause in descending and ascending (default) orders to determine the MIN and MAX values through returning values as 1 from them, respectively, in order to be able to use within the CASE statement (DENSE_RANK() function returns more than one row satisfying the related conditions in ties occuring cases among extremum values)

SELECT country, population,
       CASE
         WHEN DENSE_RANK() OVER ( ORDER BY population ) = 1 THEN 'MIN'
         WHEN DENSE_RANK() OVER ( ORDER BY population DESC ) = 1 THEN 'MAX' ELSE 'NA'
       END AS "pop_stats"
  FROM countries_by_population
 ORDER BY population DESC

Btw, do not use double quotes for derived column values, but use single quotes.

Demo

MIN MAX using CASE statement in SQL Server

3 Answers3