how to get the max value from a table join

Hi all,

I have 2 table with one having customer info like personal details. The other table has sales records of each customer. I need join these 2 tables and retrive only the latest sales record per customer. How could I do this. Any help appriciated.

Many thanks,

Cheers,

Vije

You really need to describe your problem better. Information that ought to be included is (1) which version of SQL server you are using -- whether 2000 or 2005 or what, (2) the names of the tables, and (3) the names of the relevant columns.

One potential is something like:

Code Snippet

select column1,

column2,

...

columnN

from salesTable s

join customerTable c

on s.customerKey = c.customerKey

where s.salesDate

= ( select max(s.salesDate

from salesTable t

where t.customerKey = s.customerKey

)

This probably isn't the best guess, but without a better description of the target it is hard to hit the middle of the target.

|||

Here the sample,

Code Snippet

Create Table #salesdetails (

[CustomerId] int ,

[Date] DateTime ,

[Product] int ,

[qty] int

);

Insert Into #salesdetails Values('1','1/1/2007','1','10');

Insert Into #salesdetails Values('1','2/1/2007','2','10');

Insert Into #salesdetails Values('2','2/1/2007','2','10');

Insert Into #salesdetails Values('3','2/1/2007','3','10');

Insert Into #salesdetails Values('2','4/1/2007','2','10');

Insert Into #salesdetails Values('3','5/1/2007','3','10');

Insert Into #salesdetails Values('4','2/1/2007','2','10');

Create Table #customer (

[CustomerId] int ,

[Name] Varchar(100)

);

Insert Into #customer Values('1','Hari');

Insert Into #customer Values('2','Mani');

Insert Into #customer Values('3','Viji');

Insert Into #customer Values('4','Shiv');

Code Snippet

Select det.*,Cust.[Name] From #salesdetails det

Join (Select [CustomerId],Max([Date]) LastestDatefrom #salesdetails Group By [CustomerId]) as Latest

On Latest.[CustomerId]= Det.[CustomerId] and Latest.LastestDate = Det.[Date]

Join #customer Cust

On Cust.[CustomerId] = det.[CustomerId]