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

SQL Server 2000 Forums

SQL Server Development (2000)

Help! Please!

Author

Topic

shawnrgr
Starting Member

4 Posts

Posted - 2003-11-12 : 01:35:27

[code]select title, max(retail) from bk_book,bk_order,bk_orditem
where bk_order.cust#=1017 and (bk_book.isbn = bk_orditem.isbn) and
(bk_order.order# = bk_orditem.order#) group by title;[/code]

the title (book title) and retail columns are both in the bk_book table. cust 1017 has one order with 4 books in it. I need to know the name of the most expensive book the cust bought. When i take out "title, " and "group by title" it works by returning the price of the most expensive book. but i need to know the name of the book, not the price. when I run what I have it gives me all the books the customer bought and all the prices. can someone help me out?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-12 : 02:13:06

Try this:


select top 1 title, retail from
(
select title, retail from bk_book,bk_order,bk_orditem 
where bk_order.cust#=1017 and (bk_book.isbn = bk_orditem.isbn) and
 (bk_order.order# = bk_orditem.order#)
) subquery1
order by retail desc

Is this Access or in SQL Server? I am not quite sure if this will work in Access...

Owais

Where there's a will, I want to be in it.

shawnrgr
Starting Member

4 Posts

Posted - 2003-11-12 : 02:49:13

that works but it has to just display the book title thats has the max retail. it can't display them all, any ideas?

shawnrgr
Starting Member

4 Posts

Posted - 2003-11-12 : 02:50:04

oh, its not access im using oracle sql

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-12 : 03:57:14

Doesn't my query display the title and the price of the most expensive book?

This query should return the title and price of all books bought by the customer, sorted by descending price:


select title, retail from bk_book,bk_order,bk_orditem 
where bk_order.cust#=1017 and (bk_book.isbn = bk_orditem.isbn) and
 (bk_order.order# = bk_orditem.order#)
  order by retail desc

Now if you just add a TOP 1 clause to it, the query will limit itself to the first (most expensive) book returned by the query.


select TOP 1 title, retail from bk_book,bk_order,bk_orditem 
where bk_order.cust#=1017 and (bk_book.isbn = bk_orditem.isbn) and
 (bk_order.order# = bk_orditem.order#)
  order by retail desc

Owais

Where there's a will, I want to be in it.

shawnrgr
Starting Member

4 Posts

Posted - 2003-11-12 : 09:28:43

I get this:

select TOP 1 title, retail from bk_book,bk_order,bk_orditem
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

There has to be a way around it without using TOP and using MAX instead right? I don't think I can use TOP, my professor hasn't gone over that. (this is a huge project im doing, and this is the last of the problems.)

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-11-13 : 09:05:10

Hi,

This web site specifically for Microsoft SQL Server..

":-) IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources