I have a relational db. Two pertinent tables for this question are the Account table and Membership table (1 to many). Each record in Membership is for a membership year. President wants to know who has not renewed for current year. My query is:
SELECT DISTINCT TOP (100) PERCENT YEAR(dbo.Membership.MemYear) AS MemYear, dbo.Users.NameLastForAlpha, dbo.Users.MailTo, dbo.Users.StreetAddress, dbo.Users.Address2, dbo.Users.City, dbo.StateLookup.State, dbo.Users.Zip, dbo.Users.PhoneHome, dbo.Membership.EmailNwsltr FROM dbo.Membership LEFT OUTER JOIN dbo.Users INNER JOIN dbo.StateLookup ON dbo.Users.StateID = dbo.StateLookup.StateID ON dbo.Membership.UserID = dbo.Users.UserID WHERE (dbo.Users.UserID NOT IN (SELECT UserID FROM dbo.Membership AS Membership_1 WHERE (YEAR(MemYear) = YEAR(GETDATE())))) ORDER BY dbo.Users.NameLastForAlpha
The problem is the result contains EVERY year the person was a member. This is 2016, so for example if David Bowie was a member in 2013 and 2014 he is listed twice. My question is how to have ONLY THE LAST YEAR he was a member?
The technique (row_number ...) used below, comes in handy in many situations, so if you're going to work alot with sql, you should learn this.
It makes a temporary column (rn) with a sequence for each user starting from 1 and counts up. As you want the newest memyear, we sort in reverse order.
Now it's only a matter of getting rows where rn=1, and the memyear is less than current year.
For better readability for others (and yourself later on), please get a habbit of formating you queries. Here's a cool nice-ifier.
select m.memyear
,u.namelastforalpha
,u.mailto
,u.streetaddress
,u.address2
,u.city
,s.state
,u.zip
,u.phonehome
,m.emailnwsltr
from (select userid
,year(memyear) as memyear
,emailnwsltr
,row_number() over(partition by userid order by memyear desc) as rn
from dbo.membership
) as m
left outer join dbo.users as u
on u.userid=m.userid
inner join dbo.statelookup as s
on s.stateid=u.stateid
where m.memyear<year(current_timestamp)
and m.rn=1
order by u.namelastforalpha
;
Thank you so much! I did not know about either of these capabilities " (row_number() over(partition by" so this was very helpful. I had researched msdn and other places before posting and never saw anything about this. I just tried it on my data and it worked perfectly.