Get Latest from Joining two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get Latest from Joining two tables

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-04-05 : 16:48:35

Hi there

I am trying to work out how to get the latest profile from the client table when joing this two. Basically, there are 2 tables as follow:

Client table:
ClientID INT
ClietGUID UNIQUEIDENTIFIER
FirstName VARCHAR
LastName VARCHAR
DateCreated DATETIME

Profile table:
ProfileID
ProfileGUID UNIQUEIDENTIFIER
ClientID
PhoneNo
Address
DateCreated DATETIME

The relationship is that ONE Client can have MANY PROFILE. ONE PROFILE can only be in ONE CLIENT.

When you join this obviously it will have many profile with the same client information BUT all I want just to get the latest profile (as well bring in the ProfileUniqueidentifier) for each client.

SELECT * FROM Clients c INNER JOIN 
(SELECT ClientID, MAX(ProfileID), ProfileGUID 
 FROM cms_ClientProfiles 
 GROUP BY ClientID, ProfileGUID) cp 
ON c.ClientID = cp.ClientID

But it doesn't give the latest though DUE to I am including the ProfileGUID in this query but If I remove that it will be ok.

Any ideas?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-05 : 19:47:28

Maybe this?

select a.ClientID ,a.ClietGUID ,a.FirstName ,a.LastName ,a.DateCreated ,b.ProfileID,b.ProfileGUID ,b.PhoneNo,b.Address
from Client a INNER JOIN
(select ProfileID,ProfileGUID ,ClientID,PhoneNo,Address,row_number() over(partition by ClientID order by DateCreated Desc) as rn) b
on a.ClientID = b.ClientID and b.rn = 1

dewacorp.alliances

452 Posts

Posted - 2009-04-07 : 00:08:54

Thanks Vijay that work after few alteration.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources