SQL Clone
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 (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27418 Visits: 17557
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 Modens 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 (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27418 Visits: 17557
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 Modens 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 (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27418 Visits: 17557
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 Modens 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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8103 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 (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

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

Group: General Forum Members
Points: 8103 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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91380 Visits: 41151
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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