how do i select max(order_date)

[bookouri]

I have three tables
Customer (customer_name, customer_id)
orders (customer_id, purchase_date)
account_balance (customer_id, account_bal)

Each customer has one record, each account_balance is one record, but the orders have multiple rows.

I need to select the customer_name from customer, the latest purchase_date for that customer from the orders table and their current account balance from the account_balance table. I relate the tables using the customer_id in all three tables. But I get multiple entries for each customer. select customer_name, purchase_date, account_balance from customer c, orders o, account_balance b where
c.customer_id = o.customer_id and c.customer_id= b.customer_id.. etc...

If the customer had three orders I get the customer listed three times, etc. I need to produce a report with one line per customer.
Name LatestOrderDate CurrentAccountBalance
--------------------------------------------------
Doe, John 01/01/2001 24.40
Doe, Jane 12/31/2001 500.00

Im sure the answer has got to be some kind of select max(order_date) but I cant get the right combination.

any suggestions would be appreciated...

 

[carp]

I think this will work for you:

select customer_name, purchase_date, account_balance
from customer c, orders o, account_balance b
where c.customer_id = o.customer_id
and c.customer_id= b.customer_id
and o.purchase_date = (SELECT max(purchase_date)
FROM orders o2
WHERE o2.customer_id = c.customer_id);

 

[bookouri]

thanks, I think that will do it, I tried several mutations similar to that but couldnt quite get the select max down right...

thanks again...