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
Solved

Not selecting duplicate data

Posted on 2016-10-02
6
59 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
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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