Solved

Not selecting duplicate data

Posted on 2016-10-02
6
81 Views
Last Modified: 2016-10-02
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
Comment
Question by:markej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41825393
you will need to remove Id and teamposition from your partition clause
0
 

Author Comment

by:markej
ID: 41825401
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41825405
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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 

Author Comment

by:markej
ID: 41825407
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41825410
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
 

Author Comment

by:markej
ID: 41825422
Thanks that seems to have done it.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question