max(orderdate)not working in subquery

hi
I am having problems with the max(orderdate) not generating the most recent orderdate of each customer's order. My code is this, with the results following

select firstname,lastname,orderdate,( sum(retail*quantity))"total profit"
FROM customers join orders using (customer#)
join orderitems using (order#)
join books using (isbn)
where orderdate in (select max(orderdate)
from orders
where orderdate is not null
group by orderdate)
HAVING SUM(quantity*retail) in
(SELECT SUM(quantity*retail)
FROM orders, orderitems, books
WHERE orders.order# = orderitems.order#
AND orderitems.isbn = books.isbn
GROUP BY orderitems.order#)
group by firstname, lastname, orderdate
order by firstname, lastname, orderdate;

my results:
FIRSTNAME LASTNAME ORDERDATE total profit
---------- ---------- ------------------------- ----------------------
BECCA NELSON 03-APR-05 227.5
BONITA MORALES 01-APR-05 142.26
BONITA MORALES 05-APR-05 101.05
CINDY GIRARD 31-MAR-05 26.57
CINDY GIRARD 03-APR-05 55.85
GREG MONTIASA 01-APR-05 53.19
GREG MONTIASA 05-APR-05 29.28
JAKE LUCAS 31-MAR-05 162.28
JAKE LUCAS 03-APR-05 119.74
JASMINE LEE 03-APR-05 74.48
JENNIFER SMITH 03-APR-05 74.48
KENNETH FALAH 01-APR-05 239.48
KENNETH FALAH 04-APR-05 26.57
KENNETH JONES 05-APR-05 26.57
LEILA SMITH 01-APR-05 72.55
LEILA SMITH 04-APR-05 119.74
REESE MCGOVERN 31-MAR-05 148.96
STEVE SCHELL 04-APR-05 23.84
TAMMY GIANA 02-APR-05 462.25
TAMMY GIANA 04-APR-05 58.56
THOMAS PIERSON 02-APR-05 53.14

21 rows selected

as you can see it is generating each order, there should only be I4, showing the customer's name only once, and the most recent order only. What am I doing wrong?

re: max(orderdate)not working in subquery

Hi,

There are a couple of things you are doing wrong.
First of all, you want the orderdate to be equal to the max orderdate, so loose the in keyword and replace it with '='
In the sub query, get rid of the group by, this is not needed, as you only want the max of the orderdate and no other fields.
I think you want the most recent order placed by each customer, so in the sub query, in the where clause, just get the dates of the customer from the main query.
It would be a lot easier to use aliases in this query as well.
If you have done the mentioned changes, please post your new code, if you still don't get the desired results.

Tip: Use the code tag in this forum, it makes your code much more readable for others.

Pilgrim.

re: max(orderdate)not working in subquery

Hi,

Try This query:


Regards
Veena