CODEWITHSUNDEEP
sqlaggregate-functions
ducin
ducin

Reputation: 26477

sql standard - using aggregate functions inside aggregate functions

I've got 3 tables with different structures, which share the same logical column: price. I want to find the biggest price from all records from all 3 tables. I'm trying something like:

SELECT MAX(price) FROM (

SELECT MAX(price) FROM pc
UNION
SELECT MAX(price) FROM printer
UNION
SELECT MAX(price) FROM laptop

);

but I get an syntax error: Incorrect syntax near ';'.. What is wrong and how it should look like? This should be compatible to the SQL standard, not a particular RDBMS.

Upvotes: 1

Views: 6596

Answers (2)

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this sql.

SELECT MAX(price) FROM (

SELECT MAX(price) as price FROM pc
UNION
SELECT MAX(price) as price FROM printer
UNION
SELECT MAX(price) as price FROM laptop

) t;

Upvotes: 2

John Woo
John Woo

Reputation: 263893

you need to give alias for the subquery,

SELECT MAX(price) max_price
FROM 
(
    SELECT price FROM pc
    UNION ALL
    SELECT price FROM printer
    UNION ALL
    SELECT price FROM laptop
) subquery

getting the maximum price inside the subquery is not necessary.

Upvotes: 3

Related Questions