Selecting two columns in same table multiple times

Question

I am trying to use the below query that shows country and population of second most and second least populous country. I figured out a way to select the population for those countries but I can't find any good way to implement selection of country names.

Select  Max(population) 
From country Where population < (Select max (population) From country)
Union
Select Min(population)
From country where population > (select Min(population) from country) ;

I found a way for selecting country and population for second most/second least populous country but problem is I can't use union on two selects with 2 ORDER BY (one in each select).

Any idea what I can do to solve my problem? Note: Im using Postgres

Much better implemented using `OFFSET 1 LIMIT 1` and an `ORDER BY`. — Craig Ringer, Mar 26 '14 at 11:40

score 1 · Answer 1 · answered Mar 26 '14 at 12:00

By using window function, you can do it simply like this:

with t as (
  select population,
         row_number() over (order by population desc) mx,
         row_number() over (order by population asc) mn
  from country)
select 'second most population', population from t where mx = 2
union all
select 'second least population', population from t where mn = 2;

score 1 · Accepted Answer · answered Mar 26 '14 at 12:03

select *
from (
    select country, population
    from
        (
            select country, population
            from country
            order by population
            offset 1 limit 1
        ) s

    union

    select country, population
    from
        (
            select country, population
            from country
            order by population desc
            offset 1 limit 1
        ) q
) s

score 0 · Answer 3 · edited Jun 30 '17 at 07:17

0

Found solution :

(SELECT name, population 
FROM country 
ORDER BY population Desc 
LIMIT 1 OFFSET 1
) 
UNION
(
SELECT name, population 
FROM country 
ORDER BY population Asc 
LIMIT 1 OFFSET 1);

All I needed was parentheses...

edited Jun 30 '17 at 07:17

Deem

7,007
2
19
23

answered Mar 26 '14 at 12:03

Max Mansour

77
3
8

score 0 · Answer 4 · edited May 23 '17 at 12:11

Much simpler and faster:

(
SELECT population
FROM   country
ORDER  BY 1
OFFSET 1
LIMIT  1
)
UNION ALL
)
SELECT population
FROM   country
ORDER  BY 1 DESC
OFFSET 1
LIMIT  1
);

Yes, you can use LIMIT and OFFSET for individual legs of a UNION query. Just use parentheses. Details:
Combining two SQL SELECT statements on the same table

Be sure to have an index on population to make this very fast for bigger tables.

And use UNION ALL, since you do not want to fold duplicates. Faster and actually correct.

Selecting two columns in same table multiple times

4 Answers4