CODEWITHSUNDEEP
sql
RT.
RT.

Reputation: 423

Remove Column from result in SQL

I am trying to solve an SQL challenge. The challenge is:

List the models of any type having the highest price of all products present in the database.

(The challenge is taken from https://www.sql-ex.ru/)

Giving:

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

What I was trying to do is the following:

Select model
From (Select model, Max(price) AS price
From (Select model,price From PC UNION Select model,price From Laptop UNION Select model,price from Printer) AS U
Group By model) AS M

The problem is that the question only require the maximum value while currently all the models are listed with their individual max price. If I had been using MAX(price) on the outer query the result would have contained the price column which is not allowed.

Is there a way to the take the correct models with the global Max(price) without showing the price column?

The following is a working solution that I made but it is more complicated (I am adding it just as a reference):

Select model
From (Select model,price From PC UNION Select model,price From Laptop UNION Select model,price from Printer) AS M
Where M.price IN (Select Max(price)
From (Select price From PC UNION Select price From Laptop UNION Select price from Printer) AS U)

Upvotes: 2

Views: 137

Answers (1)

Dominik
Dominik

Reputation: 103

Your relations look quite strange. How do you join tables to the product table? And why do you store model in PC, Laptop and Printer but also in product? Is this given by the task or did you do it on your own?

Anyways, regarding your question, this worked for me in Postgres. It is a little shorter than your solution and uses max().

with modelswithprices as (Select model, price From PC UNION Select model, price From Laptop UNION Select model, price from Printer)
select model from modelswithprices where price = (select max(price) from modelswithprices)

Upvotes: 1

Related Questions