sql standard - using aggregate functions inside aggregate functions

Question

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.

You should add alias in the inner queries like "Max(price) as price". try this it may be also the cause of error. Also the subquery you need to give alias — Ankur Trapasiya
– Ankur Trapasiya, Commented May 12, 2013 at 11:04

John Woo · Accepted Answer · 2013-05-12 10:57:34Z

3

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.

answered May 12, 2013 at 10:57

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ducin Over a year ago

is max_price same thing as AS max_price? And what is subquery in the last line?

John Woo Over a year ago

AS is an optional keyword so it's the same. subquery is the alias of the subquery. you can change it to what ever you want as long as it is not a reserved keywrd.

John Woo Over a year ago

by the way, what database server are you using? did you try to execute the query above?

ducin Over a year ago

Well, it's sql-ex.ru, a SQL-learning platform. They may be using MS SQL, but I'm not sure. The error message is what I got from there. This learning platform is supposed to use SQL standard. BTW what I asked is just a small part of entire question I was working on ;) thanks!

Ankur Trapasiya · Accepted Answer · 2013-05-12 11:07:57Z

2

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;

answered May 12, 2013 at 11:07

Ankur Trapasiya

2,2004 gold badges22 silver badges43 bronze badges

Collectives™ on Stack Overflow

sql standard - using aggregate functions inside aggregate functions

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related