UNION works with CTE, but not without?-CodePudding

I'm using PostgreSQL 11.12.

I have this query:

select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select max(length(first_name)) from db_employee)
order by 1 
limit 1
union
select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select min(length(first_name)) from db_employee)
order by 1 
limit 1

and when I run it I get this error:

syntax error at or near "union"

If I use CTE it works:

with cte1 as
(select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select max(length(first_name)) from db_employee)
order by 1 
limit 1),

cte2 as
(select first_name, length(first_name)
from db_employee
where length(first_name) = 
(select min(length(first_name)) from db_employee)
order by 1 
limit 1)

select * from cte1
union
select * from cte2;

Why does the first query result in syntax error? Is it possible to get it to work without using CTE?

CodePudding user response：

To include a LIMIT clause per SELECT in a UNION query, you must add parentheses. Like:

(  -- !
SELECT first_name, length(first_name)
FROM   db_employee
WHERE  length(first_name) = (SELECT max(length(first_name)) FROM db_employee)
ORDER  BY 1
LIMIT  1
) -- !
UNION
( -- !
SELECT first_name, length(first_name)
FROM   db_employee
WHERE  length(first_name) = (SELECT min(length(first_name)) FROM db_employee)
ORDER  BY 1
LIMIT  1
) -- !

Calculate difference of multiple highest and lowest column values

That said, your query can be optimized. Use instead:

(
SELECT first_name, length(first_name)
FROM   db_employee
ORDER  BY length(first_name) DESC NULLS LAST, first_name
LIMIT  1
)
UNION ALL  -- my guess
(
SELECT first_name, length(first_name)
FROM   db_employee
ORDER  BY length(first_name), first_name
LIMIT  1
);

Subtle difference: this does not completely exclude rows with first_name IS NULL, but that only shows if all rows have first_name IS NULL.

About NULLS LAST:

Sort by column ASC, but NULL values first?

CodePudding user response：

just put both the sql parts in separate brackets and keep union outside.

    (select first_name, length(first_name)
    from db_employee
    where length(first_name) = 
    (select max(length(first_name)) from db_employee)
    order by 1
    limit 1)
    union
    (select first_name, length(first_name)
    from db_employee
    where length(first_name) = 
    (select min(length(first_name)) from db_employee)
    order by 1
    limit 1);

another way of doing the same thing:

    select * from (
    select first_name, l_fname
    ,row_number() over (order by l_fname) rn_min
    ,row_number() over (order by l_fname desc) rn_max
    from(
    select first_name,length(first_name) l_fname from db_employee
    ) A
    ) b where (rn_min = 1 or rn_max = 1);