Finding Distinct Names and IDs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding Distinct Names and IDs

Author

Topic

ansarpk
Starting Member

5 Posts

Posted - 2012-06-20 : 22:04:15

Hi Everyone,

I have a table (T1) of more than 100,000 rows of unorganized data to organize, as below;

(T1)
ID FirstName LastName
1 Sam Lincon
2 Gaurav Thakur
2 Gaurv Thakur
2 Gaurav Thakur
3 Ansar Javid
4 Dinesh Shimar

I just want to send the distinct records in one file (T2) and unmatched in other file (T3) for correction, so T2 and T3.

(T2)
ID FirstName LastName
1 Sam Lincon
2 Gaurav Thakur
3 Ansar Javid
4 Dinesh Shimar

(T3)
2 Gaurv Thakur

Any tip plz? I m a junior level programmer and cannot work on complex distinct, group by, sub queries

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-20 : 22:44:13

how do you determine which is correct entry which needs to be sent to T2? whats the rule for determining that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ansarpk
Starting Member

5 Posts

Posted - 2012-06-21 : 00:30:40

Thanks for looking at it. I stated only unique (distinct) rows or its group will go in Table2 (T2). Plz see id 2 which is

2 Gaurav Thakur
2 Gaurv Thakur
2 Gaurav Thakur

So first record execute goes in T2,
Second execute has First Name change so leave it or move in T3.
3rd execute is unique so either insert in T2 or leave.

Is that ok?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 00:36:35

Nope..not clear..moving 3rd record again to T2 will create duplicates for ID 2 which is not what you want as per
I just want to send the distinct records in one file (T2) a

can you explain on this difference?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ansarpk
Starting Member

5 Posts

Posted - 2012-06-21 : 00:54:18

Yes you are right. Sending distinct records to T2 is the purpose. All the distinct records with same id + FirstName + LastName. How can I do that? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 01:10:42

you still didnt answer my first question. what according to you respresents right entry for an ID? There should be a rule specified based on which you need to select single entry for T2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ansarpk
Starting Member

5 Posts

Posted - 2012-06-21 : 01:44:45

Thanks Visakh for your concern.

I try to explain you the scenario, the data I have has duplicate names, missing names, spelling mistakes. So I need to find those duplicate/mis spelled records and clean them, e.g. Gaurav, Gaurv are different but their id is same. So whatever rule you make, I have no problem, plz tell me some way to find those duplicate records to clean.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 01:53:53

here's a way if you're not concrned on which value goes into T2 for ID


INSERT T2
SELECT ID,	FirstName,	LastName
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS Rn,*
FROM T1
)t
WHERE Rn=1

INSERT T3
SELECT ID,	FirstName,	LastName
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS Rn,*
FROM T1
)t
WHERE Rn > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ansarpk
Starting Member

5 Posts

Posted - 2012-06-21 : 02:02:43

Thanks so much, I ll try it tomorrow cuz it is 2am here. After execution, will inform you the results. ok?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-21 : 17:40:49

ok...good luck!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources