Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding and picking from Duplicates...


Finding and picking from Duplicates...

Author
Message
RedBirdOBX
RedBirdOBX
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 243
This should be simple but I' having a hard time wrapping my head around this one late on a friday afternoon.

I have a table containing people and except for one or two fields, the entire record could be a duplicate and should be treated as so. For example:

FirstName, LastName, Address, City, State, SSN, SomeNumber
***************************************************
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 001
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 004
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007


How can I write a select statement to just get the most recently created record like:
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007

???
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
RedBirdOBX (10/4/2013)
This should be simple but I' having a hard time wrapping my head around this one late on a friday afternoon.

I have a table containing people and except for one or two fields, the entire record could be a duplicate and should be treated as so. For example:

FirstName, LastName, Address, City, State, SSN, SomeNumber
***************************************************
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 001
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 004
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007


How can I write a select statement to just get the most recently created record like:
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007

???




Pretty sparse on details on here. Maybe as simple as top 1 with an Order by ?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
RedBirdOBX
RedBirdOBX
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 243
Sorry. Dealing with thousands of records and a few to several hundred are duplicates so TOP 1 wouldn't work.

The only thing I can come up with so far seems too complicated as I tend to overcomplicated things.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
RedBirdOBX (10/4/2013)
Sorry. Dealing with thousands of records and a few to several hundred are duplicates so TOP 1 wouldn't work.

The only thing I can come up with so far seems too complicated as I tend to overcomplicated things.


I am willing and able to help but you need to allow me to help. The best way you can do that is by posting a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
RedBirdOBX
RedBirdOBX
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 243
OK. Thanks. I am pulling from a poorly built table and database and inserting into a new database and tables. All works fine except for the source table could and does have duplicate persons. Shouldn't be duplicated but they are. So for example, here is just 5 records (from 7000):

[FirstName] | [MiddleName] | [LastName] | [Suffix] | [SSN] | [OwnerNumber]
* * GUROWITZ,ANDREW,SCOTT (PRESIDENT) 000000000 160
* * LANE,WILLIAM, 111111111 162
MICHAEL S (PRESIDENT) BEYER 222222222 163
* * KOREN,DANIEL,L 333333333 166
* * KOREN,DANIEL,L 333333333 174
JAMES E KOONS 444444444 182

(Yes, there are astericks in name fields. I clean that up later.)

See how "Daniel" is int there twice. Even with the same ssn? I basically need to somehow select the latest, most bottom version of Daniel. The own showing OwnerNumber = 174.

**Those OwnerNumbers are FKs which I'll extract and save later. I cannot just trash them. I'll insert them into a related table once I over come this.

**SSNs are ignored in my INSERT. I come back and grab those later.


So as you can guess, I just used this to grab these records.....

(SELECT [FirstName],[MiddleName],[LastName],[Suffix],[SSN], [OwnersNumber]
FROM ONBOARD.dbo.DealershipOwners WHERE DealershipOwnersID BETWEEN 10 AND 15)


Any idea how I can grab these most bottom version of "Daniel" (and others)?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
RedBirdOBX (10/4/2013)
OK. Thanks. I am pulling from a poorly built table and database and inserting into a new database and tables. All works fine except for the source table could and does have duplicate persons. Shouldn't be duplicated but they are. So for example, here is just 5 records (from 7000):

[FirstName] | [MiddleName] | [LastName] | [Suffix] | [SSN] | [OwnerNumber]
* * GUROWITZ,ANDREW,SCOTT (PRESIDENT) 000000000 160
* * LANE,WILLIAM, 111111111 162
MICHAEL S (PRESIDENT) BEYER 222222222 163
* * KOREN,DANIEL,L 333333333 166
* * KOREN,DANIEL,L 333333333 174
JAMES E KOONS 444444444 182

(Yes, there are astericks in name fields. I clean that up later.)

See how "Daniel" is int there twice. Even with the same ssn? I basically need to somehow select the latest, most bottom version of Daniel. The own showing OwnerNumber = 174.

**Those OwnerNumbers are FKs which I'll extract and save later. I cannot just trash them. I'll insert them into a related table once I over come this.

**SSNs are ignored in my INSERT. I come back and grab those later.


So as you can guess, I just used this to grab these records.....

(SELECT [FirstName],[MiddleName],[LastName],[Suffix],[SSN], [OwnersNumber]
FROM ONBOARD.dbo.DealershipOwners WHERE DealershipOwnersID BETWEEN 10 AND 15)


Any idea how I can grab these most bottom version of "Daniel" (and others)?


Well since you still didn't post much of anything useful I can't help you with the code. You can do this with Row_Number() over(Partition by [FirstName], [MiddleName], [LastName], [Suffix], [SSN] order by OwnerNumber desc).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
This is something you could test.


CREATE TABLE #S(FirstName VARCHAR(20), LastName VARCHAR(20), Address VARCHAR(20), City VARCHAR(20), State VARCHAR(2)
, SSN VARCHAR(20), SomeNumber VARCHAR(20))

INSERT INTO #S
SELECT 'John', 'Doe', '123 Street', 'Atlanta', 'GA', '123456789', '001' UNION ALL
SELECT 'John', 'Doe', '123 Street', 'Atlanta', 'GA','123456789', '004' UNION ALL
SELECT 'John', 'Doe', '123 Street', 'Atlanta', 'GA','123456789', '007'

;with cte
as (select row_number() over(partition by FirstName,LastName, Address order by SomeNumber DESC) as rn
,FirstName,LastName, Address,SomeNumber from #S)
SELECT * FROM cte where rn = 1

Results:
rn FirstName LastName Address SomeNumber
1 John Doe 123 Street 007




If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
RedBirdOBX
RedBirdOBX
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 243
Thanks Ron! I was just working on the Temp table approach. Will try and post back....
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 25280
RedBirdOBX (10/4/2013)
Thanks Ron! I was just working on the Temp table approach. Will try and post back....


The temp table - I only selected it, since I do NOT have a DB that I can use for testing answers to SSC questions.

The major part of the solution, which can be used on the real table (be careful and be sure to test, test and then test again) before using in production. I would suggest to copy some of the data from the real table into a test table, and then once you are sure that the code does what you need done, and nothing more, then use in production.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45386 Visits: 39940
RedBirdOBX (10/4/2013)
This should be simple but I' having a hard time wrapping my head around this one late on a friday afternoon.

I have a table containing people and except for one or two fields, the entire record could be a duplicate and should be treated as so. For example:

FirstName, LastName, Address, City, State, SSN, SomeNumber
***************************************************
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 001
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 004
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007


How can I write a select statement to just get the most recently created record like:
John, Doe, 123 Street, Atlanta, GA, 3000, 123456789, 007

???




Is the SSN encrypted in real life?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search