Returning just the lastest entry

[AnnieRider]

I am trying to return just the latest entry from a History Table but I need to link it to a details table so that it just selects the latest history for certain people.

I have tried

Select Org_Ref, max(CR_HISTORY.Client_Ref) AS "SG"
From CR_HISTORY Inner Join CR_CLIENTS on CR_HISTORY.Client_Ref = CR_CLIENTS.Client_Ref
Where Left(CR_CLIENTS.Card_Ref,2) In ('11','12')
AND CR_HISTORY.HistoryType_Ref = 'E' AND CR_HISTORY.DateStart > '20110101'
Group By CR_HISTORY.Client_Ref, Org_Ref

But I am getting more than one entry for a Client_Ref when I just want the latest.

Thanks
Annie

 

[bborissov]

Check your GROUP BY clause.
You are grouping the records by both fields.
I think you should remove Client_Ref from GROUP BY.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Take a look at the following blog post of how to solve this common problem:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library