Please help! - 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

Please help!

Author

Topic

Amber99
Starting Member

11 Posts

Posted - 2009-04-06 : 20:57:34

Whats wrong with my code!?

Question:
Exercise: 10
Find the printers having the highest price.
Result set: model, price.

My Query:
SELECT Printer.model AS MODEL, MAX(Printer.price) AS PRICE FROM Printer, Product GROUP BY Printer.model;

Result:
MODEL PRICE
1276 400.0000
1288 400.0000
1401 150.0000
1408 270.0000
1433 270.0000
1434 290.0000

BUT SHOULD BE:

model price
1276 400.0000
1288 400.0000

Thanks!!

Amber-

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)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-06 : 21:38:52

there's nothing wrong with your code it is just answering a different question. Your code is showing the highest price for EVERY model instead of finding just the model(s) which has the highest price.

Is this a test of just homework?

Be One with the Optimizer
TG

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-06 : 23:05:21

Try this once,

declare @temp table (MODEL int, PRICE decimal(18,3))
insert into @temp select 1276, 400.0000
insert into @temp select 1288, 400.0000
insert into @temp select 1401, 150.0000
insert into @temp select 1408, 270.0000
insert into @temp select 1433, 270.0000
insert into @temp select 1434, 290.0000

select * from @temp where price = ( select max(price) from @temp)

onlyforme
Starting Member

25 Posts

Posted - 2009-04-07 : 02:23:36

Hi ,
I think u need to list top 2 model according to price.For that try the following query.

select model,price from tablename where price in (select Top 4 price from tablename order by price desc) order by price desc

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-07 : 03:01:25

quote:
Originally posted by onlyforme

Hi ,
I think u need to list top 2 model according to price.For that try the following query.

select model,price from tablename where price in (select Top 4 price from tablename order by price desc) order by price desc

no this works only for the above set of data.
next time may not give the desired result with some other data.
nageswars work for this
tanx..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-07 : 10:20:12

if sql 2005,


select model,price
from
(
select model,price,max(price) over () as maxprice from tablename
)t
where price=maxprice

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-07 : 10:47:20

although that is a cool trick I don't think it is particularly efficient. compare the output of "set statistics io on" for that statement compared to:


select model, price
from   tablename
where  price = (select max(price) from tablename)

I think you'll find a lot more scan and read overhead using the OVER() technique.

But it doesn't matter anyway, now that the OP has gotten sqlteam to do her homework for her

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123232
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123238
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123318

I'm sure she is off having unprotected sex with several of here close friends

hmm, that may have been somewhat offensive - OP, please know that that was a joke and I'm just jealous because no one wants to...oh, nevermind.

Be One with the Optimizer
TG

Amber99
Starting Member

11 Posts

Posted - 2009-04-07 : 18:55:07

lol I would get mad BUT you did help TG and so did everyone else... I still have to try the other code... your code works perfect and seems something I can understand... would have been nice if it had remarks on it!

select model, price
from Printer
where price = (select max(price) from Printer) // Why the nested select? I know it works but how did you know to add that?

Thanks everyone!!

Amber-

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-07 : 19:17:25

:) glad you didn't get TOO mad.

>>would have been nice if it had remarks on it!
sounds like every boss I've ever had :) I'll tell you what I told them: What could possibly make this clearer than the code itself?

>>Why the nested select?...how did you know to add that?
the nested select is called a sub-query. experience I guess - the value I wanted to match could be obtained by that simple query.

"Find the printers having the highest price."

>>Find the printers:
select model, price from printers

>>the highest price:
select max(price) from printers

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources