sql aggregation function using

Question

There are several tables in DB:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, price, screen)
Printer(code, model, color, type, price)

and I need to find

Find the model number of the product (PC, laptop, or printer) with the highest price. Result set: model.

I managed to write the following query:

select model from 
(select model, price from PC
union
select model, price from Laptop
union
select model, price from Printer) G

and now I need to plot the model/models from set G, wich have a max price

I can easily select a max price by adding to select clause - max(G.price), but i need models and only models...

what syntax would be right?

thank you in advance!

Are you sure you want to be using UNION and not UNION ALL? — Kermit
– Kermit, Commented Jan 2, 2013 at 19:27
@tania Since it seems you are having a hard time getting the correct answer, can you post some sample data for each table and the the desired result? — Taryn
– Taryn, Commented Jan 2, 2013 at 20:36

Jacco · Accepted Answer · 2013-01-02 21:46:00Z

1

Highest price only?

select model
from
(select 
   model, 
   rank() over (order by max(price) desc) as [rank] 
from 
  (select model, price from PC
   union
   select model, price from Laptop
   union
   select model, price from Printer) u
   group by model) g
where g.rank = 1

I'm sorry, but I'm currently not able to test if we can use MAX() inside of a rank(). If not, add another sub query. First determine the max(price), then the rank.

So, MAX() inside of RANK() is working... An alternative, with a simple syntax:

select top 1 with ties 
  g.model
from
(select 
   u.model, 
   max(u.price) as [maxPrice] 
from 
  (select model, price from PC
   union
   select model, price from Laptop
   union
   select model, price from Printer) u
   group by model) g
order by g.maxPrice desc

edit 1: added 'top 1' since only a single record is expected
edit 2: removed 'top 1', added rank
edit 3: removed 'partition by model', after using sqlfiddle. Thanks @bluefeet!
edit 4: added alternative 'with ties'. Again, thanks to @bluefeet.

edited Jan 2, 2013 at 21:46

answered Jan 2, 2013 at 19:30

Jacco

3,2701 gold badge22 silver badges30 bronze badges

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

6 Comments

tania Over a year ago

it returns the whole bunch of the models in a db... not just one with the highest price

Jacco Over a year ago

Sorry, thought you needed all highest prices, I will add 'top 1'.

tania Over a year ago

with ONE highest price :) there might be ties between models, so there might be from one to many returns, but with the same(one) max price...

Jacco Over a year ago

Okay, sorry didn't think of that one. Answer will be updated.

Taryn Over a year ago

@Jacco FYI on the TOP 1 just add WITH TIES and it will return multiple

|

tania · Accepted Answer · 2013-01-03 14:14:45Z

1

here is the solution which works..

SELECT model FROM pc WHERE price >= ALL(SELECT MAX(price) FROM pc UNION SELECT MAX(price) FROM laptop UNION SELECT MAX(price) FROM printer)

UNION

SELECT model FROM laptop WHERE price >= ALL(SELECT MAX(price) FROM pc UNION SELECT MAX(price) FROM laptop UNION SELECT MAX(price) FROM printer)

UNION

SELECT model FROM printer WHERE price >= ALL(SELECT MAX(price) FROM pc UNION SELECT MAX(price) FROM laptop UNION SELECT MAX(price) FROM printer)

answered Jan 3, 2013 at 14:14

tania

1,0862 gold badges14 silver badges32 bronze badges

1 Comment

Dawood ibn Kareem Over a year ago

Yes, this works. It would be even more efficient with UNION ALL in place of UNION each time it occurs. Also, you don't need the MAX in the subselects, because of the >= ALL.

DNadel · Accepted Answer · 2013-01-02 19:52:53Z

0

Building on Jacco's answer,

select model
from
(select model, max(price) as maxPrice 
from 
  (select model, price from PC
   union
   select model, price from Laptop
   union
   select model, price from Printer) u
   group by model) g
where maxPrice = max(maxPrice)

change to limit 1 instead of mysql.

edited Jan 2, 2013 at 19:52

answered Jan 2, 2013 at 19:45

DNadel

4851 gold badge5 silver badges13 bronze badges

1 Comment

tania Over a year ago

not really... An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference

user985189 · Accepted Answer · 2013-01-02 20:01:38Z

0

SELECT
    model
FROM 
    (
    SELECT model, price, ROW_NUMBER() OVER(ORDER BY price DESC) AS seq FROM PC
    UNION
    SELECT model, price, ROW_NUMBER() OVER(ORDER BY price DESC) AS seq FROM Laptop
    UNION
    SELECT model, price, ROW_NUMBER() OVER(ORDER BY price DESC) AS seq FROM Printer
) final
WHERE
    seq = 1

answered Jan 2, 2013 at 20:01

user985189

4 Comments

tania Over a year ago

dont' quite understand what are you doing in this query, but it returns, the highest prices within each type, eg model with highest price for printer, model with highest price for Laptop and for PC,

tania Over a year ago

and should return just one model/many models with highest price (only one price is the highest)in the whole DB, o

user985189 Over a year ago

@tania - If that is what you are trying to do, then why doesn't Ulises answer work for you??

user985189 Over a year ago

@tania - You just want the model (and only the model) of the product with the highest price, correct?

Collectives™ on Stack Overflow

sql aggregation function using

4 Answers 4

6 Comments

1 Comment

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related