OT: SQL Question ..., what's the easiest way to ... |
|
Porsche, and the Porsche crest are registered trademarks of Dr. Ing. h.c. F. Porsche AG.
This site is not affiliated with Porsche in any way. Its only purpose is to provide an online forum for car enthusiasts. All other trademarks are property of their respective owners. |
|
OT: SQL Question ..., what's the easiest way to ... |
SirAndy |
Mar 17 2005, 07:11 PM
Post
#1
|
Resident German Group: Admin Posts: 41,644 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
SQL Question:
what's the easiest way to find duplicates in a table? let's say i have a table (tbl_Users) with 20000 rows and the table has a column for the users email addy (sEmail) and i want to list all the accounts that have duplicate email addys. i could run a script that takes each row and runs a count(*) on that addy, but that'll take forever. anything simpler than that ??? (IMG:http://www.914world.com/bbs2/html/emoticons/idea.gif) Andy |
Sparky |
Mar 17 2005, 07:15 PM
Post
#2
|
Mahna Mahna! Group: Members Posts: 1,134 Joined: 21-June 03 From: Spencer, MA Member No.: 847 |
SELECT
FROM tablename GROUP BY HAVING count(*) > 1 But then again my sql is a bit rusty... My best, Mike D. |
Part Pricer |
Mar 17 2005, 07:17 PM
Post
#3
|
Believe everything I post Group: Benefactors Posts: 1,825 Joined: 28-December 02 From: Danbury, CT Member No.: 35 |
I've done this with MySQL tables. I've just exported the table and imported it into Excel. Then sort by the column you want. Then, add a column that contains a formula that is true if a value is the same as the value in the row above it.
|
john rogers |
Mar 17 2005, 07:20 PM
Post
#4
|
Senior Member Group: Members Posts: 1,525 Joined: 4-March 03 From: Chula Vista CA Member No.: 391 |
Do it like this:
sqlplus> delete from userID.my_email_data a where rowid < (select max(rowid) from userID.my_email_data b where b.address = a.address and b.username = a.username); Notes: userID is the schema owner, my_email_data is the name of the table and address, username are the fields to check for dupes to. You can not use any functions such as substr(....) or you'll get inconsistant results. Additionally, the email field entries have to be exacly alike, even to case or it will fail cause then they aren't really duplicates! |
SirAndy |
Mar 17 2005, 07:34 PM
Post
#5
|
||
Resident German Group: Admin Posts: 41,644 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
ok, how the heck do i sort a column in excel by name ???? (IMG:http://www.914world.com/bbs2/html/emoticons/confused24.gif) Andy |
||
SirAndy |
Mar 17 2005, 07:37 PM
Post
#6
|
||
Resident German Group: Admin Posts: 41,644 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
ok, got it ... that worked! (IMG:http://www.914world.com/bbs2/html/emoticons/beerchug.gif) Andy |
||
Part Pricer |
Mar 17 2005, 07:37 PM
Post
#7
|
Believe everything I post Group: Benefactors Posts: 1,825 Joined: 28-December 02 From: Danbury, CT Member No.: 35 |
Select the entire worksheet. Then go to Data then Sort. Choose the column you want to sort by.
|
JB 914 |
Mar 17 2005, 07:38 PM
Post
#8
|
||||
Daddy! Finish my car NOW!!! Group: Members Posts: 1,593 Joined: 26-September 04 From: Garden Grove, CA Member No.: 2,831 |
highlight the rows ( without the column header row at the top) then use data\filter\advanced filter to sort the data. they have a check box for unique records. hope this helps |
||||
Part Pricer |
Mar 17 2005, 07:41 PM
Post
#9
|
||
Believe everything I post Group: Benefactors Posts: 1,825 Joined: 28-December 02 From: Danbury, CT Member No.: 35 |
Choosing Unique Records will hide the dupes. |
||
JB 914 |
Mar 17 2005, 07:49 PM
Post
#10
|
||||
Daddy! Finish my car NOW!!! Group: Members Posts: 1,593 Joined: 26-September 04 From: Garden Grove, CA Member No.: 2,831 |
then you can import the now "good" records table back to SQL |
||||
Part Pricer |
Mar 17 2005, 07:51 PM
Post
#11
|
Believe everything I post Group: Benefactors Posts: 1,825 Joined: 28-December 02 From: Danbury, CT Member No.: 35 |
I think Andy just wants to identify the dupes. I think he would cause some problems if he abitrarily deleted the record that appeared second for duplicate email addresses. What if he was deleting the wrong account?
|
JB 914 |
Mar 17 2005, 08:06 PM
Post
#12
|
Daddy! Finish my car NOW!!! Group: Members Posts: 1,593 Joined: 26-September 04 From: Garden Grove, CA Member No.: 2,831 |
|
SirAndy |
Mar 17 2005, 08:21 PM
Post
#13
|
||
Resident German Group: Admin Posts: 41,644 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
yeah, i have to go through them by hand and figure out which ones are used and which ones are the ghosts ... i found 531 duplicate accounts ... (IMG:http://www.914world.com/bbs2/html/emoticons/wacko.gif) Andy PS: i changed the code so now it catches duplicate email addys upon registration. should have done that in the first place. oh well ... (IMG:http://www.914world.com/bbs2/html/emoticons/headbang.gif) |
||
Lo-Fi Version | Time is now: 19th May 2024 - 01:46 AM |
All rights reserved 914World.com © since 2002 |
914World.com is the fastest growing online 914 community! We have it all, classifieds, events, forums, vendors, parts, autocross, racing, technical articles, events calendar, newsletter, restoration, gallery, archives, history and more for your Porsche 914 ... |