My problem is i have a two tablestable1customercustid custnametable2custaddrcustaddrid custaddrstr custid datei have different records for each custid in custaddr table. say for example for the first customer we have 6 records in custaddr table. so i want the latest dated record from the custaddr table. ie to find the maximum date. pls somebody can write the query for this, i have tried but not able to find the max of the dates.thanks in advance

Questions by kiran_stealth@yahoo.com

Showing Answers 1 - 2 of 2 Answers

rk

  • Apr 24th, 2006
 

select c.*, ca.custaddrstr,ca.custdate from customer c, custaddr ca

where c.CUSTID = ca.CUSTID

and ca.CUSTDATE in (select max(custdate) from custaddr

group by custid)

  Was this answer useful?  Yes

dj_dj_dj

  • Mar 18th, 2010
 

Table1: Customer Table2: Custaddr
Cust_id Cust_name Cust_addr_id Cust_addr Cust_id Date
1 Dharam 10 Mumbai CST 1 01-Jan-10
2 Rits 20 Mumbai Bhandup 2 15-Jan-10
3 Sonal 30 Mumbai Ghatkopar 1 13-Jan-10
40 Mumbai Vile Parle 1 15-Jan-10
50 Mumbai Andheri 2 17-Jan-10
60 Mumbai Thane 2 19-Jan-10
90 Mumbai Mulund 3 19-Jan-10
70 Mumbai Nahur 3 21-Jan-10
80 Mumbai Nahur 1 23-Jan-10

Querry would be like this:

SELECT ca.cust_id,c.cust_name,max(add_date)
FROM custaddr ca, customer c
WHERE ca.cust_id = c.cust_id
GROUP BY ca.cust_id, c.cust_name

Hope this helps you..
Regards
Dharmendra Jaiswal

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions