hi,
i have 2 tables: CUST and ORDERS
CUST contains cust_num, cust_name, cust_phone
ORDERS contains cust_num, order_date, order_amt
i want to list all the cust_names and their last order date and amount
how would i code this?
thanks.

Use join to retrive record from both the tables by joining on CUST_NUM field. then use group by.

i know about join. how would the code look like, please?

i want to list all the customer names and then latest date and amount for that date for every customer.

here's the correct answer that i figured out:
SELECT cu.cust_name,
od.order_amount
FROM customers cu,
orders od
WHERE cu.cust_id = od.cust_id
and od.order_date =
(select max(order_date) from orders od
where od.cust_id = cu.cust_id)

I am glad to know that you found the solution before i do that for you. Happy programming.

[quote=alidabiri;456278]here's the correct answer that i figured out:
SELECT cu.cust_name,
od.order_amount
FROM customers cu,
orders od
WHERE cu.cust_id = od.cust_id
and od.order_date =
(select max(order_date) from orders od
where od.cust_id = cu.cust_id)[/QUOTE]

here's the correct answer that i figured out:

That's not a great way to do it, because you've got a subquery. Doing two queries when only one is needed.

You'll find it should perform faster if you do a join and then get the max all in one queery, something like below. It depends on how Oracle optimises it though, performance issues can be fun to play with. In general the following will be better than what you've got above:


Having said that, it really does depend on a number of factors which I won't get into for now.... anyway you have some options now