Selecting the most recent record within a query?

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  December 9, 2013 at 9:58 am

  #285758

  Hi

  I probably should know this, but I dont ..

  I am using this

  SELECT ASSESSMENT.Code, Client.LName, Client.FName, USER_DEFINED_DATA.EffDate, Client.ID

  FROM dbo.Client AS Client INNER JOIN

  dbo.TreatmentPlan_Helper ON Client.OID = dbo.TreatmentPlan_Helper.Client_OID INNER JOIN

  dbo.ASSESSMENT AS ASSESSMENT INNER JOIN

  dbo.USER_DEFINED_DATA AS USER_DEFINED_DATA ON ASSESSMENT.OID = USER_DEFINED_DATA.ASSESSMENT_MONIKER ON

  dbo.TreatmentPlan_Helper.TreatmentPlan_OID = USER_DEFINED_DATA.ATTACHED_TO_OID

  WHERE (USER_DEFINED_DATA.CAD750 = 'Final') AND (ASSESSMENT.Code LIKE 'ch3%')

  ORDER BY client.lname, client.fname, USER_DEFINED_DATA.EffDate DESC

  Which gives me a client list like below

  CH3AdamsAmanda2013-08-01 00:00:00.0001111

  CH3AdamsAmanda2013-07-01 00:00:00.0001111

  CH3Jones Amanda2013-07-01 00:00:00.0001222

  CH3Jones Amanda 2013-09-01 00:00:00.0001222

  What I want to see is

  CH3AdamsAmanda2013-08-01 00:00:00.0001111

  CH3Jones Amanda2013-07-01 00:00:00.0001222

  Thanks IN Advance

  Joe

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  December 9, 2013 at 10:05 am

  #1672041

  So I think I answered my own question...

  SELECT ASSESSMENT.Code, Client.LName, Client.FName, max(USER_DEFINED_DATA.EffDate), Client.ID

  FROM dbo.Client AS Client INNER JOIN

  dbo.TreatmentPlan_Helper ON Client.OID = dbo.TreatmentPlan_Helper.Client_OID INNER JOIN

  dbo.ASSESSMENT AS ASSESSMENT INNER JOIN

  dbo.USER_DEFINED_DATA AS USER_DEFINED_DATA ON ASSESSMENT.OID = USER_DEFINED_DATA.ASSESSMENT_MONIKER ON

  dbo.TreatmentPlan_Helper.TreatmentPlan_OID = USER_DEFINED_DATA.ATTACHED_TO_OID

  WHERE (USER_DEFINED_DATA.CAD750 = 'Final') AND (ASSESSMENT.Code LIKE 'ch3%')

  --ORDER BY client.lname, client.fname, USER_DEFINED_DATA.EffDate DESC

  group by ASSESSMENT.Code, Client.LName, Client.FName, Client.ID

  So do I have to include all fields in the Select in the group by?

• Grant Fritchey

  SSC Guru

  Points: 398886

  More actions

  December 9, 2013 at 12:34 pm

  #1672098

  Another set of mechanisms for getting versioned data (the latest) are illustrated in this article[/url] I wrote for Simple-Talk.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning