[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
0
markej
Asked:
markej
  • 3
  • 3
1 Solution
 
É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
 
É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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Éric MoreauSenior .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
 
markejAuthor Commented:
Thanks that seems to have done it.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now