Help with MIN function

[emtboy9]

hello,

Obviously, I am not a SQL coder... but I am trying to learn SQL in my spare time. Right now, I am using three different books: SQL Queries for Mere Mortals, the O'Reilly SQL Pocket Guide, and an Oracle9i SQL text that I found at a used book store (its part of Oracle's DBA cert program).

So, I need to find the answer to this:
List the most expensive book purchased by customer 1017.

I would think that this would do it:
select title, MIN(retail) from books, orders, orderitems
where orders.customer# = 1017
and orders.order# = orderitems.order#
and orderitems.isbn = books.isbn
Group by title

But it does not. IT DOES go as far as listing everything that that person ordered, but it does not show only the one item with the minimum retail cost.

How do you seperate results? If I run it without the group by, it does not work. If I try a HAVING like this:

HAVING MIN(retail) = retail
it does not work. That one I thought would work, to match only the record where the retail field matches MIN(retail) for the whole column...

any ideas?

 

[dreameR25]

select title, retail
from books, orders, orderitems
where orders.customer# = 1017
and orders.order# = orderitems.order#
and orderitems.isbn = books.isbn
and retail = (select max(retail)
		from books b
		where books.isbn = b.isbn)

 

[emtboy9]

Thanks!

Now just for curiosity sake, is there a way to do this without a nested select statement?

Is SQL somewhat like perl in that there are many different ways to accomplish the same task?

Jeff

 

[dreameR25]

Strictly speaking the above is not actually a nested select statement but a correlated sub query.

A nested query would look something like :

SELECT a.title,
       (SELECT codeName from Codes where CodeID = 1),
       a.price
FROM books

Of course there are many ways to achieve the same results in SQL but which method you use depends on the data that you're working with.

There are always different ways of achieving the same result but try and break down the problem in order to find different solutions. Based on your initial query, if you are to take out the title from the SELECT clause and re-run the query, it should return the highest valued book for customer (you used min function but you said you want the most expensive anyway...). But returning just the price of the book is not enough which is why I used a correlated subquery as we need to return the details of the most expensive book.

Read up on correlated sub-queries. If you can understand the concept of these queries it will open many SQL doors for you, but one step at a time!