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
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
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
markej
you will need to remove Id and teamposition from your partition clause
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
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.
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks that seems to have done it.