How can I get distinct data from one col

Question

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

what output you are getting from above query and your expected output. — DarkRob, Aug 07 '19 at 09:50
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Aug 07 '19 at 10:08
Possible duplicate of [SELECT DISTINCT on one column](https://stackoverflow.com/questions/966176/select-distinct-on-one-column) — Bartosz X, Aug 07 '19 at 10:25
I see no `ROW_NUMBER()` in your query. What code exactly have you tried? — Serg, Aug 07 '19 at 10:34
You say that you want one "member" record, but none of your tables are called "member". It am unclear on what you want. — Gordon Linoff, Aug 07 '19 at 10:44

score 2 · Accepted Answer · answered Aug 07 '19 at 10:57

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

score 0 · Answer 2 · answered Aug 07 '19 at 10:16

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```

DarkRob · Answer 3 · 2019-08-07T10:43:45.303

0

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

edited Aug 07 '19 at 10:43

answered Aug 07 '19 at 10:35

DarkRob

3,843
1
10
27

score 0 · Answer 4 · answered Aug 07 '19 at 10:46

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;

How can I get distinct data from one col

4 Answers4