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

In query, we are selecting the date as max date using max function but suppose if the last inserted row is the date less than the existing dates [it means this is the last record we have inserted in table] & if we want to retrieve this row...then max function would fail.
In this scenario, how to retrieve the last row?

The query would be :
[this is just a sample query]