Only show the latest date in a selection of data

• jon.clay 91459

  SSC-Addicted

  Points: 487

  More actions

  January 2, 2018 at 8:36 am

  #342195

  Hi there everyone

  I'm having problems using the MaxDate function in SQL and wondered if you could please help me?

  I have the following scenario whereby a ContactID field can appear several times and yet have different lines of SQL that show different renewal dates. This is for a membership organisation that has a different renewal date each year, just so you get the picture. So, I might have something like this:

  ContactID                 RenewalDate
  1                               01/07/2018
  1                               01/07/2017
  1                               01/07/2016
  2                               01/07/2015
  2                               01/07/2014
  3                               01/07/2018
  3                               01/07/2017
  3                               01/07/2016

  I'm trying to create a statement that will run through the Subscriptions table and only pull out the highlighted entries shown above (i.e. the latest date for each ContactID).

  I've tried various SQL scripts but none of them seem to work.

  Many thanks
  Jon

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 2, 2018 at 8:38 am

  #1974073

  Please post table definitions and sample data (as INSERT statements) if you want a query written.

  If you just want advice, then you can use ROW_NUMBER, partition by ContactID Order by RenewalDate DESC and filter for that = 1

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• jon.clay 91459

  SSC-Addicted

  Points: 487

  More actions

  January 2, 2018 at 8:48 am

  #1974076

  Thanks Gail, but I'm not sure how to achieve that.
  I've been doing this (which I thought might work), but I still get multiple rows per ContactID

  SELECT ContactID, RenewalDate
  FROM SubscriptionMembers
  WHERE RenewalDate IN
  (SELECT MAX(RenewalDate)
  FROM SubscriptionMembers
  GROUP BY ContactID)
  ORDER BY ContactID

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 2, 2018 at 8:50 am

  #1974079

  GilaMonster - Tuesday, January 2, 2018 8:38 AM

  Please post table definitions and sample data (as INSERT statements) if you want a query written.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Grant Fritchey

  SSC Guru

  Points: 398910

  More actions

  January 2, 2018 at 9:27 am

  #1974091

  Here's a set of methods on how you can deal with versioned data. Instead of a GROUP BY, I'd suggest using a TOP 1 with an ORDER BY. That's generally going to result in superior performance.

  "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

• jon.clay 91459

  SSC-Addicted

  Points: 487

  More actions

  January 2, 2018 at 9:39 am

  #1974093

  Many thanks for your help everyone.

  Best wishes
  Jon

• below86

  SSChampion

  Points: 11553

  More actions

  January 2, 2018 at 10:08 am

  #1974101

  You should be able to use the MAX to get what you want, unless I'm not understanding your issue.

  DECLARE @sample TABLE
(
    ContactID INT,
    RenewalDate DATE
)
;

  INSERT INTO @sample
SELECT 1, '01/07/2018'
UNION ALL
SELECT 1, '01/07/2017'
UNION ALL
SELECT 1, '01/07/2016'
UNION ALL
SELECT 2, '01/07/2015'
UNION ALL
SELECT 2, '01/07/2014'
UNION ALL
SELECT 3, '01/07/2018'
UNION ALL
SELECT 3, '01/07/2017'
UNION ALL
SELECT 3, '01/07/2016'

  SELECT ContactID, MAX(RenewalDate) AS RenewalDate
FROM @sample
GROUP BY ContactID
ORDER BY ContactID
Results:
  ContactID    RenewalDate
       1        2018-01-07
       2        2015-01-07
       3        2018-01-07
  

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  January 2, 2018 at 10:25 am

  #1974104

  jon.clay 91459 - Tuesday, January 2, 2018 8:36 AM

  Hi there everyone

  I'm having problems using the MaxDate function in SQL and wondered if you could please help me?

  I have the following scenario whereby a ContactID field can appear several times and yet have different lines of SQL that show different renewal dates. This is for a membership organisation that has a different renewal date each year, just so you get the picture. So, I might have something like this:

  ContactID                 RenewalDate
  1                               01/07/2018
  1                               01/07/2017
  1                               01/07/2016
  2                               01/07/2015
  2                               01/07/2014
  3                               01/07/2018
  3                               01/07/2017
  3                               01/07/2016

  I'm trying to create a statement that will run through the Subscriptions table and only pull out the highlighted entries shown above (i.e. the latest date for each ContactID).

  I've tried various SQL scripts but none of them seem to work.

  Many thanks
  Jon

  Is this your requirement?

  
create table renewal
(
contactid int,
renewaldate date
);
insert into renewal values (1,'01/07/2016');
insert into renewal values (1,'01/07/2017');
insert into renewal values (1,'01/07/2018');
insert into renewal values (2,'01/07/2014');
insert into renewal values (2,'01/07/2015');
select
contactid,
MAX(renewaldate)
from renewal
group by contactid


  Saravanan