•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 427,774 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,712 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Oracle advertiser: Programming Forums
Views: 5198 | Replies: 7
 |
| |
•
•
Join Date: Oct 2007
Posts: 5
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
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.
•
•
Join Date: Feb 2007
Location: Bangalore,India
Posts: 1,290
Reputation:
Rep Power: 4
Solved Threads: 83
Use join to retrive record from both the tables by joining on CUST_NUM field. then use group by.
Share your Knowledge.
•
•
Join Date: Oct 2007
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
i know about join. how would the code look like, please?
•
•
Join Date: Oct 2007
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
i want to list all the customer names and then latest date and amount for that date for every customer.
•
•
Join Date: Oct 2007
Posts: 5
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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)
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)
•
•
Join Date: Dec 2007
Posts: 6
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
[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]
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]
•
•
Join Date: May 2007
Posts: 22
Reputation:
Rep Power: 2
Solved Threads: 0
Newbie Poster
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
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)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:
select cu.cust_name, od.order_amount, max(order_date)
from customers cu
join orders od
on (cu.cust_id = od.cust_id)
group by cu.cust_name, od.order_amountHaving 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
Last edited by peter_budo : Mar 10th, 2008 at 8:23 pm. Reason: Keep It Organized - please use [code] tags
|
•
•
•
•
•
•
•
•
DaniWeb Oracle Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Hybrid Mode
- mysql_fetch_array(): supplied argument is not a valid MySQL (PHP)
- AJAX generated <select> and FIREFOX (JavaScript / DHTML / AJAX)
- How to select the Last row from a table..using Mysql (MySQL)
- can i select the 2nd row/record from a table (MySQL)
- javascript to add select button (JavaScript / DHTML / AJAX)
- ASP .NET database hit counter (ASP.NET)
- Find and Replace (Database Design)
Other Threads in the Oracle Forum
- Previous Thread: oracle9i operating system audit file location
- Next Thread: Administrating a database using VB
