Home » SQL & PL/SQL » SQL & PL/SQL » Latest Record
Latest Record [message #302050] Fri, 22 February 2008 16:17 Go to next message
sirisha.yada@gmail.com
Messages: 2
Registered: February 2008
Junior Member
Hi All,

I want to get the customers latest address .

customer table is having cutomerID,Customer name and address table has addrid,customerid,address

how to write the query.

i have tried

Select Customers.CustomerID,
(select max(Addr_ID) from Address where Customers.CustomerID=Address.CustomerID )as a from
Customers
group by CustomerID

but this gives all the records from customer instead i want only records from address table and which are latest address of that customer.
Any help is appreciated.

Thanks,
Sirisha
Re: Latest Record [message #302051 is a reply to message #302050] Fri, 22 February 2008 16:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What column Identifies an address as being the 'latest'
Re: Latest Record [message #302052 is a reply to message #302051] Fri, 22 February 2008 16:31 Go to previous messageGo to next message
sirisha.yada@gmail.com
Messages: 2
Registered: February 2008
Junior Member
CutomerID column
Re: Latest Record [message #302054 is a reply to message #302052] Fri, 22 February 2008 16:39 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
So the CustomerID column tells you which Address is the latest? Hmmm, I think that you are going to have to post a test case (Create table and insert scripts to accurately represent your data.
Re: Latest Record [message #302073 is a reply to message #302050] Sat, 23 February 2008 00:43 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use a scalar subquery in SELECT which returns a row for each row of your table, use an inline view in FROM clause and a join or a nested subquery in WHERE clause to restrict the source to the wanted rows.

Regards
Michel
Previous Topic: Query to Know Product Languages of my database..
Next Topic: does rebuild indexs generates a redo
Goto Forum:
  


Current Time: Mon Dec 05 21:34:01 CST 2016

Total time taken to generate the page: 0.11051 seconds