Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding and picking from Duplicates... Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 11:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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

???


Post #1501707
Posted Friday, October 4, 2013 11:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 13,452, Visits: 12,315
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)
Post #1501712
Posted Friday, October 4, 2013 11:45 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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.
Post #1501715
Posted Friday, October 4, 2013 11:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 13,452, Visits: 12,315
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)
Post #1501718
Posted Friday, October 4, 2013 12:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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)?
Post #1501722
Posted Friday, October 4, 2013 1:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 13,452, Visits: 12,315
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)
Post #1501744
Posted Friday, October 4, 2013 1:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 5,617, Visits: 25,215
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
Post #1501764
Posted Friday, October 4, 2013 2:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
Thanks Ron! I was just working on the Temp table approach. Will try and post back....
Post #1501765
Posted Friday, October 4, 2013 2:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 5,617, Visits: 25,215
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
Post #1501771
Posted Friday, October 4, 2013 5:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:41 PM
Points: 37,078, Visits: 31,638
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1501792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse