Avoiding repeated subqueries? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Avoiding repeated subqueries?

Author

Topic

lcblank
Starting Member

10 Posts

Posted - 2015-04-24 : 18:39:02

The database scheme consists of four tables:
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)
The table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

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

Sorry about formatting. I am having a really hard time making sql legible.

select model 
from 
(select model, price
from pc
union 
select model, price
from laptop
union select model, price 
from printer) as a
where 
price=(select max(a.price)
from
(select model, price
from pc
union 
select model, price
from laptop
union select model, price 
from printer) as a)

I use the subquery


(select model, price
from pc
union 
select model, price
from laptop
union select model, price 
from printer)

twice. I was wondering if there was a way to avoid this. Thanks for the help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-24 : 19:47:10

Here are some ideas:


select top 1 model
from 
(
	select model, row_number() over (order by price desc) as pricerowno
	from 
	(
		select model, price
		from pc
		union 
		select model, price
		from laptop
		union select model, price 
		from printer
	) as a
) as b
order by pricerowno asc

select model
from
(
	select model, rank() over (order by price desc) as rankprice
	from 
	(
		select model, price
		from pc
		union 
		select model, price
		from laptop
		union select model, price 
		from printer
	) as a
) as b
where rankprice = 1

If it's a common union that you'll need to do, I'd also throw it into a view.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

lcblank
Starting Member

10 Posts

Posted - 2015-04-25 : 09:37:55

Thank you for the help! I am not familiar with views. So, I will have to read up!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources