Not selecting duplicate data

I have some historical data where users have been allocated multiple ids I can't remove the duplicates as they are used elsewhere in the application and I don't have access to change that part. The duplicates are in the teammembers table

and this is my query

SELECT t.id, t.FirstName, t.Surname,  t.teamposition as Position, c.seasonid
FROM (SELECT * , ROW_NUMBER() OVER (PARTITION BY id,firstname, surname, teamposition
                           ORDER BY ID DESC
                                       ) AS rn
FROM teammembers) as t LEFT JOIN PlayerSeasonLnk as c on t.id = c.teammemberid AND c.seasonid=6
WHERE (t.ClubId = 44) AND (t.TeamPosition <> 5)and teamid = 362
order by surname

What I need is data that has only one entry for each Firstname, Surname combination.

HELP
Book1.xlsx
markejAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
I just saw that you already have an alias, your called it rn

create the sample in SQLFiddle and I'll get you the full query
0
 
Éric MoreauSenior .Net ConsultantCommented:
you will need to remove Id and teamposition from your partition clause
0
 
markejAuthor Commented:
I've just tried that
SELECT t.id, t.FirstName, t.Surname,  t.teamposition as Position, c.seasonid
FROM (SELECT * , ROW_NUMBER() OVER (PARTITION BY firstname, surname
                           ORDER BY ID DESC
                                       ) AS rn
FROM teammembers) as t LEFT JOIN PlayerSeasonLnk as c on t.id = c.teammemberid AND c.seasonid=6
WHERE (t.ClubId = 44) AND (t.TeamPosition <> 5)and teamid = 362
order by surname

Unfortunatley still have duplicates see
id      FirstName      Surname      Position      seasonid
22080      Antar      Adams      3      NULL
22086      Elijah      Akinlusi      2      NULL
22062      Dan      Akpoveta      3      NULL
47113      Matteo      Badchkam      4      6
23614      Matteo      Badchkam      4      NULL
47102      Lekan      Bakare      4      6
22083      Greg      Barton      2      NULL
22975      Lekan      Bekare      4      NULL
23304      Matt      Bishop      1      NULL
47104      Matt      Bishop      1      6
47132      Alex      Chambers      4      6
22059      Alex      Chapman      3      NULL
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Éric MoreauSenior .Net ConsultantCommented:
because you do your join on the PlayerSeasonLnk table after your select.

add an alias to your ROW_NUMBER and add "AND YourRowNumberAlias = 1" to your where clause.

Create a sample of your structure and data on http://sqlfiddle.com/ and it will be easier for us to provide a full working solution.
0
 
markejAuthor Commented:
How do I "add an alias to your ROW_NUMBER and add "AND YourRowNumberAlias = 1" to your where clause." I'm not sure what you mean could you show me an example?
0
 
markejAuthor Commented:
Thanks that seems to have done it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.