CODEWITHSUNDEEP
sqlsql-server
hattybse
hattybse

Reputation: 43

How can I get distinct data from one col

I need to get member personal data for all our members whose subscriptions have lapsed i.e. have a subscription end date before 31/03/2020, however I want to show one member record only (distinct by membership number) ideally the most recent one

I've tried a ROW_NUMBER() solution SQL - Distinct One Col, Select Multiple other? and a cross apply solution sql distinct, getting 2 columns but I can't get it to work.

SELECT membershipnumber AS Id, 
       subscription.enddate 
FROM   [dbo].[userprofile] 
       INNER JOIN dbo.subscription 
               ON userprofile.id = subscription.userprofileid 
       INNER JOIN dbo.subscriptiontype 
               ON subscriptiontype.id = subscription.subscriptiontypeid

Output is

Id  Enddate
1   2006-04-01 00:00:00.000
1   2001-04-01 00:00:00.000
1   1999-04-01 00:00:00.000
1   1998-04-01 00:00:00.000
1   2008-04-01 00:00:00.000
1   2007-04-01 00:00:00.000
1   2011-04-01 00:00:00.000
1   2005-04-01 00:00:00.000
1   2000-04-01 00:00:00.000
1   1997-04-01 00:00:00.000
2   1999-04-01 00:00:00.000
2   2012-04-01 00:00:00.000
2   2004-04-01 00:00:00.000
2   2001-04-01 00:00:00.000
2   2018-04-01 00:00:00.000
2   2009-04-01 00:00:00.000
2   2005-04-01 00:00:00.000
2   1997-04-01 00:00:00.000

Desired output

Id Enddate

1   2011-04-01 00:00:00.000
2   2018-04-01 00:00:00.000

Upvotes: 2

Views: 106

Answers (4)

Divyesh Jani
Divyesh Jani

Reputation: 311

Solved sql answer 

;WITH cte 
     AS (SELECT membershipnumber                        AS Id, 
                subscription.enddate, 
                Row_number() 
                  OVER ( 
                    partition BY membershipnumber 
                    ORDER BY subscription.enddate DESC) AS rownumber 
         FROM   [dbo].[userprofile] 
                INNER JOIN dbo.subscription 
                        ON userprofile.id = subscription.userprofileid 
                INNER JOIN dbo.subscriptiontype 
                        ON subscriptiontype.id = subscription.subscriptiontypeid 
        ) 
SELECT * 
FROM   cte 
WHERE  rownumber = 1

https://stackoverflow.com/a/6841644/5859743

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I suspect you want something like this:

select *
from (select . . ., -- all the columns you want
             row_number() over (partition by Membershipnumber as Id order by s.Enddate) as seqnum
      from [dbo].[UserProfile] up inner join
           dbo.Subscription s
           on up.Id = s.UserProfileId inner join
           dbo.SubscriptionType st
           on st.id = s.SubscriptionTypeId
     ) x
where  seqnum = 1;

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

If you are getting as above mentioned output. Then from that, your desired output will easily get using distinct.

  ; with cte as (
     ----- query which gives you above mentioned output
  )
   select distinct id, max(Enddate) as Enddate from cte

Upvotes: 0

asmgx
asmgx

Reputation: 8004

Not sure if I got your question right.

but you can use DISTINCT in the SELECT, that would show only one record for each member.

SELECT DISTINCT Membershipnumber as Id
,'P' as PartyType
,'A' as Status
,case
when Name = 'Standard Membership paid annually.' and EndDate > '2020-03-31' then 'Member'
when Name = 'Lapsed subscription renewal' and EndDate > '2020-03-31' then 'Member'
when Name = '3 Year Subscription (members outside of UK and Ireland, Jersey, Guernsey and the Channel Islands)' and EndDate > '2020-03-31' then 'Overseas member'
when Name = '1 Year Subscription (members outside of UK and Ireland, Jersey, Guernsey and the Channel Islands).' and EndDate > '2020-03-31' then 'Overseas member'
when Name = 'Lapsed subscription renewal' and EndDate > '2020-03-31' then 'Member'
when Name = 'Lifetime membership' then 'Lifetime member'
when Name = 'Retired membership paid annually' and EndDate > '2020-03-31' then 'Retired member'
else 'Non member'
end As MemberType
,Title as NamePrefix
,FirstName as FirstName
,Surname as LastName
,DateOfBirth as BirthDate
,'Home' as AddressPurpose
,'Default' as CommunicationReasons
,AddressLine1
,AddressLine2
,AddressLine3
,Addressline4 as CityName
,'' as CountrySubEntityName
,Country as CountryCode
,'' as CountryName
,Postcode as PostalCode
,EmailAddress as Email
FROM [dbo].[UserProfile]
  inner join dbo.Subscription on
  UserProfile.Id = Subscription.UserProfileId
  inner join dbo.SubscriptionType on
  SubscriptionType.id = Subscription.SubscriptionTypeId```

Upvotes: 0

Related Questions