Link to home
Start Free TrialLog in
Avatar of markej
markejFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

you will need to remove Id and teamposition from your partition clause
Avatar of markej

ASKER

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
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.
Avatar of markej

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of markej

ASKER

Thanks that seems to have done it.