SQLTeam.com | Weblogs | Forums

Simple... But Im Rusty


#1

Im trying to create a select statement with a join that displays the Name from table 1 Members and only one record for each member from table two (Dues) based on the MAX date

Table one (Members)
Member ID Name
152 Frank Perdue
152 Frank Perdue
10 Bill Smith
10 Bill Smith

Table Two (Dues) has many records for each member for tracking when they pay their annual dues.
Member ID Renewal Date
152 2/17/2015
152 2/6/2016
10 1/31/2015
10 3/3/2016

Based on the example data I would only want to display the following as these are value that represent the MAX date from table two.
Member ID Renewal Date Name
152 2/6/2016 Frank Perdue
10 3/3/2016 Bill Smith


#2

Like this:

select m.memberid
      ,m.name
      ,max(renewaldate)
  from members as m
       inner join dues as d
               on d.memberid=m.memberid
 group by m.memberid
         ,m.name
;