pf » Finding Duplicates with SQL

Finding Duplicates with SQL

databases

Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

Via Huajun Zhai's Blog. Also see How to remove duplicate rows from a table - Microsoft Knowledge base article.



Related Entries
196 people found this page useful, what do you think?

Trackback Address: 169/3EECA31943FD659BFE62CDC156DBABFD
On 10/07/2004 at 3:26:47 AM MDT Anonymous wrote:
1
select distinct(email) from users

This works little quicker than your solution.

On 10/07/2004 at 3:09:50 PM MDT Anonymous wrote:
2
doesn't distinct pull all unique entries so if there was a duplicate entry it would still pull 1 of those as a distinct record? it wouldn't pull only records that occur just once.

On 10/21/2004 at 2:27:10 PM MDT Anonymous wrote:
3
Great Solution! Thanks

On 11/19/2004 at 6:25:20 PM MST Emanuel wrote:
4
In T-SQL you may use "insert" to remove duplicates. What you must do is create another work table with the IGNORE_DUP_KEY option set. So you copy the data from one table to the other. Drop the original table and rename the work table to your original table name and that's it. you have a plain, clean, non duplicated records table. Something like:

Create table tableCleanDup (idfield int, field1 varchar(30), field2 varchar(30)) Create unique index removeduplicates on tableCleanDup (field1,field2) with IGNORE_DUP_KEY

insert tableCleanDup select * from tableOriginal

it will send a message "duplicate key was ignored" but that is fine.

On 03/09/2005 at 3:43:14 PM MST Cmiller wrote:
5
This was very simple and it works, Thanks

On 05/05/2005 at 5:55:39 PM MDT Pradeep Chalise wrote:
6
I have a following table A:

W X Y Z

+ A B 1 + A B 2 * C D 5 * E F 1 # E F 3 # G H 4 + A B 1

W, X, Y, Z are column names.

I am trying to achieve the following output:

W X Y Z

+ A B 1 + A B 2 + A B 1

How could I acheive this? Any help would be appreciated. Thanks

On 05/10/2005 at 4:30:58 PM MDT John wrote:
7
Ummm...

DELETE FROM A WHERE A.W <> +

???

On 05/10/2005 at 4:34:25 PM MDT John wrote:
8
BTW... the guy who said "use select distinct" and that it works and that it is quicker is totally WRONG...

SELECT DISTINCT will not (a) give you only the duplicated records, as the original solution part 1 did, nor will it (b) give you only the singleton records, as the original solution part 2 did.

SELECT DISTINCT will give you all the distinct e-mail addresses (so in a table with entries A, B, C, C, D, E, F, A, C, X, Y we would get distinct output A, B, C, D, E, F, X, Y).

Sorry anonymous buddy.

On 05/27/2005 at 9:46:14 AM MDT LB wrote:
9
What if you wanted to find duplicates based on more than one field? For example, you have name and email as columns. You want to find if an employee has the same email listed twice.

On 06/15/2005 at 8:42:29 AM MDT Artie wrote:
10
What if you wanted to find duplicates based on more than one field? For example, you have name and email as columns. You want to find if an employee has the same email listed twice.

I actually had to do this the other day. It is very similar... just place an "AND" in your statement.

SELECT name, email COUNT(name) AS NumOccurName, COUNT(email) as NumOccurEmail FROM users GROUP BY Name, Email HAVING ( COUNT(name) > 1 ) AND ( COUNT(email) > 1)

On 06/15/2005 at 9:45:32 AM MDT Emanuel Costa wrote:
11
Artie, the example I posted (comment #4) is perfect for this scenartio. Not only for one or two fields but for any number that you specify in the unique index.

On 06/24/2005 at 8:41:35 AM MDT Anonymous wrote:
12

On 08/23/2005 at 5:37:07 PM MDT murali wrote:
13
i want to get all the duplicate records in the table but i dont wont the original data .for eg, murali is a table name which has many names as one of its field ,in that mani name is repeated for ten times,iwant to get just nine times of 'mani' name from the table

On 09/07/2005 at 6:25:13 PM MDT David wrote:
14
None of those solutions work for me. I used: SELECT T1.* FROM T1, T2 WHERE T1.C1 = T2.C1 AND T1.C2 = T2.C2 AND T1.ID <> T2.ID

On 09/26/2005 at 7:39:30 AM MDT Wayne wrote:
15
Regarding > "SELECT name, email COUNT(name) AS NumOccurName, COUNT(email) as NumOccurEmail FROM users GROUP BY Name, Email HAVING ( COUNT(name) > 1 ) AND ( COUNT(email) > 1)"

How would I actually delete all the duplicate entries that had the duplicates based on more than one field?

On 09/29/2005 at 3:30:59 PM MDT Klyve wrote:
16
I cannot tell you how long I have spent looking for an answer to this!

Many thanks all

Klyve Dawson

On 10/07/2005 at 3:25:55 PM MDT T Mac wrote:
17
Great solutions!!!

On 10/19/2005 at 2:38:27 AM MDT Sumit wrote:
18
How can i drop more than one table in a single command ( table names having some characters common)??

On 10/19/2005 at 11:49:06 AM MDT Emanuel Costa wrote:
19
I have done you home work. You can easily change this piece of t-sql code to be a stored procedure and then execute in a single command.

declare @tablestring varchar(30), @tablename varchar(50), @dropst varchar(255) set @tablestring = 'temptable' -- put the table keyword here declare dropcursor cursor forward_only for select rtrim(ltrim(name)) as name from dbo.sysobjects where type = 'u' and rtrim(ltrim(name)) like '%'+@tablestring+'%' -- you may change this to match yours requirements open dropcursor fetch dropcursor into @tablename while (@@fetch_status=0) begin set @dropst = 'drop table '+@tablename print @dropst exec(@dropst)

fetch dropcursor into @tablename end close dropcursor deallocate dropcursor

On 10/24/2005 at 5:42:01 PM MDT Mooky Desai wrote:
20
I have a complete table 'A' and a subset of that table called table 'B'. How do i remove the entries listed in table 'B' from table 'A'??? TIA

On 10/27/2005 at 1:26:31 PM MDT Frustrated wrote:
21
I have a similar problem and none of the above answers appear to work. I have 2 databases. One is the master with all email records. The second has only "Anytown" email addresses. I need to remove all the "Anytown" email addresses that appear in the master? What is the the proper SQL query to not only find the dupes but acutally delete them from the Master table?

On 11/09/2005 at 5:59:51 AM MST Dilip wrote:
22
Thanks! It works fine.

On 11/10/2005 at 4:20:33 PM MST Carl wrote:
23
Ok -- I see how you can get the count and values of the dupes, which is great -- but how does one get all rows that dupes?

On 11/14/2005 at 4:45:54 PM MST Anonymous wrote:
24
Hi try this!

delete master where email in (select email from anytown);

On 11/15/2005 at 12:55:51 AM MST Mar11b wrote:
25
I have a very similar task to do. But the criteria I need is to create a file of our client details including name, address, email. If there are any duplicates of either name, address or email, that record needs to be inserted to a differeent file. So in other words, removing all rows with duplicate field data, but rather than deleting the duplicates, insert them to a different file.

Any suggestions would be greatly appreciated.

On 11/16/2005 at 8:27:17 AM MST Seb wrote:
26
Wayne : "How would I actually delete all the duplicate entries that had the duplicates based on more than one field?"

Have you found a solution for this ? Thanks for sharing !

On 11/16/2005 at 10:12:32 AM MST shri wrote:
27
How can i find out same row in sql table? Pls give me the query for tht. Thanks.

On 11/24/2005 at 11:42:13 AM MST Sangeetha K wrote:
28
I want the query as soon as possible.I have a table with duplicate records with company,first and last name repeating.I need to put all duplicates in 1 table and all unique records in the other.

On 11/25/2005 at 5:32:47 AM MST sam wrote:
29
i am working on a table that contains client details consisting of Name, Number and Response fields now i want to include a 4th field that would show the number of occurence based on Name, number and response.

can some one help me on that.

On 11/30/2005 at 7:56:36 AM MST Justin wrote:
30
im trying to delete data from table a if its in table b been trying for a while cant get it to work please help

On 12/02/2005 at 5:16:59 AM MST Baljeet Kiroriwal wrote:
31
Great solution for fetching multiple records.

On 12/07/2005 at 7:53:29 AM MST jimesh wrote:
32
I want to get the column names in a row from a database table where the values are 0

On 12/07/2005 at 7:53:53 AM MST jimesh wrote:
33
I want to get the column names in a row from a database table where the values are 0 how can i write the query

On 12/13/2005 at 1:21:57 AM MST Mohit Dadu wrote:
34
Good I like it . Mogambo kush hua !! Ha Ha Mohit

On 12/13/2005 at 11:16:29 PM MST PH wrote:
35
I've got a table of purchases by clients (CustomerID, OrderID, OrderDate, Amount). The same customer might have placed more than 1 order, so might appear more than once in the table. How can I select the latest order for each customer (so, all customers would appear only once, but include the latest order date and amount)?

On 12/16/2005 at 10:11:45 AM MST AJ wrote:
36
I have a similar problem to PH (last post). I need to obtain the last of a series of entries in a table for a client. In Access I could use a group by option called LAST. no such feature in SQL Server. Any help

On 12/18/2005 at 1:51:30 AM MST Mark wrote:
37
Hey there, I don't know allot about MYSQL but I tried this using your query.

Regarding query:

SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 )

I did:

SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( NumOccurrences > 1 )

I tried it and it seemed to work. Would it reduce the COUNT time because I don't need to use the COUNT() function again or is it the same thing?

Cheers, Mark adcoil.com

On 12/27/2005 at 6:37:18 AM MST Nailesh wrote:
38
To Get Duplicate Values form table do this: add one more identity column in table. for example we have customer table like this -customer_id [identity], name, email

Query

SELECT a.customer_id,a.name,a.email from customer a inner join customer b on a.name=b.name and a.email=b.email abd a.customer_id<>b.customer_id

I think this will work

On 01/06/2006 at 7:16:42 PM MST amir wrote:
39
What if i wanted to delete the rows. How would I do that with PHP and MYSQL. I have been looking for an answer to this for days.

On 01/15/2006 at 1:29:16 AM MST Arvind Singh wrote:
40
I want to select distinct record from any table . Which is the best method. (According to Performance)

On 01/16/2006 at 1:04:17 PM MST Brian Moore wrote:
41
if i have a table with 10 columns and one of the columns (column 2) contains some duplicates. I want to delete the whole row if it has a duplicate value in column 2. So if i encounter a value of "3" in the the second column in 3 rows in the table. My results should just be the entire row of one of the records. Is there any simple way of doing this?

On 01/25/2006 at 4:47:29 AM MST Anonymous wrote:
42
If your table has 10 columns and one needs to be unique use the method mentioned above

e.g. if column field7 needs to be unique

Create table tableCleanDup (field1 int, field2 varchar(30), field2 varchar(30) etc....) i.e. create a new table with the same column defs as the original

Create unique index removeduplicates on tableCleanDup (field7) with IGNORE_DUP_KEY

insert tableCleanDup select * from tableOriginal

and then if you need/want to delete table table Original

insert tableOriginal select * from tableCleanDup

drop table tableCleanDup

David

On 01/25/2006 at 7:33:37 AM MST David Adedeji wrote:
43
I need to find duplicates in a field called ssn and within the duplicates I only want duplicates in different states not same states.

On 02/02/2006 at 1:19:36 PM MST Anonymous wrote:
44
How would I select all of the information in the duplicate rows, kinda like (select * from table GROUP BY id HAVING ( COUNT(id) > 1 )). "Group BY" only allows one row to be selected instead of the whole row. Any suggestions? -thanks in advance!

On 02/02/2006 at 1:28:25 PM MST david wrote:
45
Hi

I found my dups but how do I display the different columns (fields) of the duplicated records (ie:name, address, phone, etc). I tried to use "where exists" along with the count stmt but that displays all recs

dave

On 02/02/2006 at 9:00:18 PM MST SG wrote:
46
To delete duplicate rows in the same table(after you have found the N duplicates), use the following :

set rowcount <N-1> delete from tablename where <condition>

On 02/03/2006 at 9:26:37 AM MST Anonymous wrote:
47
I have found a solution to my problem of displaying a record that has a duplicate and a count of how many exist. Here is the script: --select all fields literaly select empno,ename,job,mgr,hiredate,sal,comm,deptno, count(*) from tempemp --group by all fields literaly group by empno,ename,job,mgr,hiredate,sal,comm,deptno having count(*) > 1 order by count(*) desc,sal asc;

I hope this helps someone else!

On 02/06/2006 at 1:37:19 PM MST Anonymous wrote:
48
I have the following query

select oc_name,a.BUSINESS_TITLE_DESCR,count( a.sex) F from ws_sps_ee a,ws_sps_dept b where a.deptid=b.deptid and a.BUSINESS_TITLE_DESCR in ('First Vice President','Vice President','Senior Vice President')and a.sex in('F') group by oc_name,a.BUSINESS_TITLE_DESCR,a.sex order by oc_name

i need to get the count of people who hold a particular businees title and how many of them are female and how many are male under oc_name.

can anyone help me?

On 02/08/2006 at 3:42:49 AM MST Sam wrote:
49
Thank you it works and saved me a lot of time.

On 02/09/2006 at 9:18:22 AM MST test wrote:
50
test

On 02/09/2006 at 9:31:21 AM MST lalith wrote:
51
I have two tables say A(m,p,n as elements) and B(q,r,m as elements), i want to print elements which are common in both tables in the following format

element name Table name m A m B

thanks for ur help

On 02/17/2006 at 1:46:50 PM MST chat wrote:
52
How to find out the SQL query that was used to create a view in DB2

Tx for the help in advance.

On 02/20/2006 at 8:51:25 PM MST Mark wrote:
53
I have a Table with over 2000records. I want to select the first x chars (CONCAT - fine) of aparticualr field, then count how many records in this column are the same. e.g. Column1 Count 12345 2 12345 12121 1

Does this make sense? Any ideas to to accomplish this? Thanks folks!

On 03/04/2006 at 4:39:24 PM MST Walsh wrote:
54
Thanks a lot!

On 03/07/2006 at 12:00:50 AM MST aya wrote:
55
hi there...i'm new here...hoping to get some help.i'm writing a query that's suppose to return the number of class groups for each grade. instead when i use count(class_id)... where grade=10 it returns the number of learners in that grade instead of the number of class_id(class groups) in that grade.

can anyone help?

On 03/07/2006 at 7:23:38 PM MST Steve wrote:
56
Hi,

Need Help. I am backing up one table to another but I only want to copy the data that does not already exists in the backup table because backing up every row takes to long. Is there am easy way to do this?

On 03/07/2006 at 10:50:42 PM MST Ashish Chauhan wrote:
57
I want to get the column names in a row

http://quadricit.co.nr

On 03/07/2006 at 10:52:03 PM MST http://quadricit.co.nr wrote:
58
Need Help. I am backing up one table to another but I only want to copy the data

On 03/08/2006 at 8:49:12 PM MST sudha wrote:
59
The First one is working fine. Thank you

On 03/08/2006 at 9:03:25 PM MST Anonymous wrote:
60
hello jimesh, Try this query to get column names from a table.

select column_name from information_schema.columns where table_name='pub_info'

where table_name=<<Give the table name from which u want the columns. >>

On 03/08/2006 at 9:03:43 PM MST sudha wrote:
61
hello jimesh, Try this query to get column names from a table.

select column_name from information_schema.columns where table_name='pub_info'

where table_name=<<Give the table name from which u want the columns. >>

On 03/13/2006 at 1:35:47 AM MST Jatin wrote:
62
Gr8 Solution. Thks

On 03/13/2006 at 6:11:10 AM MST Struggling New User wrote:
63
I have a list (one column) of 2000 part numbers, of which about 1200 are distinct. I figured out the "select distinct" part to get a listing of the 1200 distinct ones.

However, what I really want to know is how many times each of the distinct entries appear: I'm trying to figure out how to have a two column table, one column listing the distinct entries, one listing the number of occurences. Any help? Thx in advance.

On 03/13/2006 at 6:31:03 AM MST Miller wrote:
64
Struggling New User: Try select distinct count(part_number), part_number from part_number_table group by part_number;

On 03/13/2006 at 6:35:52 AM MST Struggling New User wrote:
65
Miller: Thanks a lot, it does the job perfectly! Cheers.

On 03/13/2006 at 7:50:06 AM MST Miller wrote:
66
Steve: I believe this can be accomplished through a MERGE statement, otherwise you will have to use PL/SQL loops and multiple DML statements.Simple syntax: MERGE INTO table1 t1 USING table2 t2 ON (t1.id_key=t2.id_key) WHEN MATCHED THEN UPDATE SET t1.first_field=t2.first_field, t1.second_field=t2.second_field WHEN NOT MATCHED THEN INSERT VALUES(t2.column1, t2.column2);

On 03/14/2006 at 4:18:30 AM MST Miller wrote:
67
Mark: Try this: SELECT LTRIM(field_name,5), COUNT(LTRIM(field_name,5)) FROM table_name GROUP BY LTRIM(field_name,5);

On 03/14/2006 at 7:05:35 AM MST Miller wrote:
68
Anonymous: Try this: SELECT t1.count(oc_name),t1.oc_name,t2.title,t1.sex FROM table1 t1, table2 t2 WHERE t1.id=t2.id AND title IN ('President','Vice_President') AND sex='male' GROUP BY t1.oc_name,t2.title,t1.sex UNION SELECT t1.count(oc_name),t1.oc_name,t2.title,t1.sex FROM table1 t1, table2 t2 WHERE t1.id=t2.id AND title IN ('President','Vice_President') AND sex='female' GROUP BY t1.oc_name,t2.title,t1.sex;

On 03/14/2006 at 7:05:40 AM MST Miller wrote:
69
Anonymous: Try this: SELECT t1.count(oc_name),t1.oc_name,t2.title,t1.sex FROM table1 t1, table2 t2 WHERE t1.id=t2.id AND title IN ('President','Vice_President') AND sex='male' GROUP BY t1.oc_name,t2.title,t1.sex UNION SELECT t1.count(oc_name),t1.oc_name,t2.title,t1.sex FROM table1 t1, table2 t2 WHERE t1.id=t2.id AND title IN ('President','Vice_President') AND sex='female' GROUP BY t1.oc_name,t2.title,t1.sex;

On 03/14/2006 at 7:05:45 AM MST Miller wrote:
70
Anonymous: Try this: SELECT t1.count(oc_name),t1.oc_name,t2.title,t1.sex FROM table1 t1, table2 t2 WHERE t1.id=t2.id AND title IN ('President','Vice_President') AND sex='male' GROUP BY t1.oc_name,t2.title,t1.sex UNION SELECT t1.count(oc_name),t1.oc_name,t2.title,t1.sex FROM table1 t1, table2 t2 WHERE t1.id=t2.id AND title IN ('President','Vice_President') AND sex='female' GROUP BY t1.oc_name,t2.title,t1.sex;

On 03/20/2006 at 12:18:22 AM MST Mark wrote:
71
Miller - Thanks! That worked great! Thanks again

On 03/20/2006 at 6:48:14 AM MST wiley wrote:
72
Hi All,

I need help in making a query that displays all names and counts how many times it occurred..

say, I have this table:

names action name1 ship name2 ship name3 ship name4 ship name5 ship name1 ship name1 ship

I want to have like this:

names totalship name1 3 name2 1 name3 1 name4 1 name5 1

pls do help... thanks

On 03/20/2006 at 6:50:40 AM MST wiley wrote:
73
Hi All,

I need help in making a query that displays all names and counts how many times it occurred..

say, I have this table:

names action name1 ship name2 ship name3 ship name4 ship name5 ship name1 ship name1 ship

I want to have like this:

names totalship name1 3 name2 1 name3 1 name4 1 name5 1

pls do help... thanks

On 03/20/2006 at 6:50:48 AM MST wiley wrote:
74
Hi All,

I need help in making a query that displays all names and counts how many times it occurred..

say, I have this table:

names action name1 ship name2 ship name3 ship name4 ship name5 ship name1 ship name1 ship

I want to have like this:

names totalship name1 3 name2 1 name3 1 name4 1 name5 1

pls do help... thanks

On 03/20/2006 at 7:22:30 AM MST learn sql wrote:
75
Damn.. such basic questions.... RTFM!

Select names, count(*) from table order by names

On 03/21/2006 at 2:51:53 AM MST wiley wrote:
76
an error would display, that names is not a single- group-group function

On 03/21/2006 at 9:37:32 AM MST Brandon wrote:
77
I wanted to find all fields which were duplicated X number of times in my database. Thanks to this post, I figured it out. Thanks!

In fact, I went further and found not only the dupes, but just *how* duplicated they are. This's my query:

SELECT field3, COUNT (field3) AS count FROM table2 GROUP BY field3 HAVING ( COUNT (field3) > 1) ORDER BY count DESC, field3

The results start with the entries duplicated the *most*, and continue on to the ones duplicated only twice.

Anyway, yer post helped me out a lot, and next time I'm in New York, I ought to buy you a drink.

On 03/22/2006 at 1:49:39 AM MST Akhil wrote:
78
how to delete the same record from same column and rowid2

On 03/23/2006 at 5:42:34 PM MST Assistance wrote:
79
Good morning Guru In need of assistance

I was told that no question is a dumb question so here goes ? Don?t know how to explain my ques but here goes, when I run this query i do not want the result to return duplicate values if I insert distinct it removes all values Any idea what im missing from the code in order for the result to look like this. It would be greatly appreciated Thanks alot Suplier Supp_cnt Invoice Invoice_cnt 1000029 1 1088 5 1000029 1 1089 2 1000029 1 1092 2

Current Code and Result!!! SELECT Supplier, count(Supplier) AS suppl_cnt, SUBSTRING(Invoice, 1, 4) AS Invoice, count(SUBSTRING(Invoice, 1, 4)) AS inv_cnt, COUNT(*) AS Expr1 FROM ApInvoice WHERE (InvoiceYear = 2005) GROUP BY Supplier, Invoice HAVING 1 < (Select distinct COUNT(Supplier) From ApInvoice Where (InvoiceYear = 2005));

RESULT

Suplier Supp_cnt Invoice Invoice_cnt Expr_cnt 1000029 1 1088 1 1 1000029 1 1088 1 1 1000029 1 1088 1 1 1000029 1 1088 1 1 1000029 1 1088 1 1 1000029 1 1089 1 1 1000029 1 1089 1 1 1000029 1 1092 1 1 1000029 1 1092 1 1

On 03/28/2006 at 12:56:00 PM MST Yo Man wrote:
80
How do I display all the fields in the record in which two of the fields are duplicate? In other words, show me all the records in there entirety where two selected field values are the same?

On 04/03/2006 at 1:38:25 PM MDT Jake wrote:
81
I have a similar scenario as Yo Man (#82) has. I have a table from which I need to pull distinct rows based on only 2 fields. Any duplicates should return only the most recent row. In the example rows 2 & 3 have duplicate values for fields fld2 & fld3.

For Example:

TABLE FIELDS fld1, fld2, fld3, fld3, fld4, fld5, myDate

DATA Row 1- 11, 22, 33, 44, 55, '11/11/2005' Row 2- 111, 222, 333, 444, 555, '4/1/2006' Row 3- 1111, 222, 333, 4444, 5555, '4/3/2006' Row 4- 11111, 2222, 3333, 44444, 55555, '1/1/2006'

DESIRED RESULT 11, 22, 33, 44, 55, '11/11/2005' 1111, 222, 333, 4444, 5555, '4/3/2006' 11111, 2222, 3333, 44444, 55555, '1/1/2006'

Thanks in advance for any help!

On 04/07/2006 at 1:56:53 PM MDT Milelr wrote:
82
wiley, Obvously EVERYONE should RTFM. Here is what you want: SELECT field1, COUNT(*) FROM table GROUP BY field1; Remember to include any fields which you are SELECTing other than functions (in this case COUNT(*)) in the GROUP BY clause. That will take care of your "not a single group function" error.

On 04/12/2006 at 1:33:45 PM MDT Pvedi wrote:
83
Select * from table1 Where KEY_ID IN (Select MIN(KEY_ID) FROM table1 Group by REPEATED FIELDS Having count(REPEATED FILEDS) > 1

On 04/17/2006 at 5:16:05 AM MDT prashant wrote:
84
How to find out column name of the table by passing only field number to the procedure.

On 04/17/2006 at 7:11:17 AM MDT Miller wrote:
85
Yo Man: Here is a query that displays all records in a table where two selected field values are the same in Oracle syntax. SELECT * FROM table WHERE same_value_field1 = same_value_field2; I hope that is what you are looking for.

On 04/18/2006 at 11:27:18 PM MDT pdelaurentis wrote:
86
Here's a case for a wiki I'm building... There are topics, and each topic has multiple revisions for different languages.

I want to first show the version in the target language... and if not, fall back on English (this way, the Wiki starts out filled in for the user of any language).

It's like taking the following two queries and merging them together so that I have distinct topic_id's and don't miss any topics... and always making sure the current language wins.

SELECT topic_id, title FROM revisions WHERE topic_id=:parent_id AND language_id=:current_language

SELECT topic_id, title FROM revisions WHERE topic_id=:parent_id AND language_id=1

# in this example, language 1 is english

Any ideas? Performance is important since there could be a large # of topics.

Thanks, Pete

On 04/25/2006 at 4:37:32 PM MDT rakesh wrote:
87
how many times 3 occurs from 3 to 3333

On 04/26/2006 at 8:59:21 AM MDT raza wrote:
88
how many times 3 occurs from 3 to 3333?

Not sure how that question relates to sql but that's an easy equation:

restated as how many times x occurs from y to z:

n = (z / x) - (y / x)

If I'm way off topic here i appologize, I'm just browsing through the posts here...

On 04/27/2006 at 2:14:14 PM MDT jeremy wrote:
89
Thanks, I was looking for exactly that. Do I even have to think anymore....?

On 05/11/2006 at 9:50:28 AM MDT Walt wrote:
90
Worked for me except I needed to check a Unigueidentifier field and SQL will not allow a count on that data type.

On 05/15/2006 at 10:28:16 PM MDT Vasily wrote:
91
Hello,

Could I do this? SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email ORDER BY created_date desc HAVING ( COUNT(email) > 1 )

Will it work with ORDER BY? I need to start from latest record.

On 05/23/2006 at 9:56:06 AM MDT jj wrote:
92
Great Solution. I was looking for this, just typed it in google and saw your page. Thanks.

On 06/08/2006 at 6:56:18 PM MDT Sujit wrote:
93
How would I select all the information in the duplicate rows,like (select * from table GROUP BY code HAVING ( COUNT(code) > 1 )). "Group BY" only allows one row to be selected instead of the whole row. Any suggestions? Sorry this is the the similer question asked earlier. Thanks in advance!

On 06/16/2006 at 12:37:35 AM MDT PH wrote:
94
Sujit, please create a sub query, in which you identify the duplicate rows, and use it to select the corresponding rows from the entire table, like this: SELECT * FROM [Table] WHERE [Code] IN (SELECT [Code] FROM [Table] GROUP BY [Code] HAVING COUNT([Code]) > 1)

On 06/21/2006 at 8:01:28 PM MDT tbsdy wrote:
95
Haven't checked this to thoroughly, but try the following:

select * from users where email=( select email from users group by email, name having (count(email) > 1) and (count(name) > 1)) and name=(select name from users group by email, name having (count(email) > 1) and (count(name) > 1))

On 06/23/2006 at 4:51:29 AM MDT Ranjeet Kumar Bhatia wrote:
96
count how many records in two tables and return sum of both tables

On 07/10/2006 at 8:23:28 AM MDT bogdan wrote:
97
how can this work if i want to group the emails after the subject and to return their email id?

On 08/18/2006 at 6:49:14 AM MDT Vijay wrote:
98
I have a table with dups and i wish to select the distinct records and insert to another table based on 3 column condition,i need to select few columns based on the 3 col condition.This may be a repeat qn but i am not sure of the answer, can anyone help ? Cheers,Vijay

On 09/15/2006 at 9:46:24 AM MDT Kevin wrote:
99
Thank you for this SQL snippet! You really saved me some serious time :)

On 09/20/2006 at 12:52:01 AM MDT Hi2u wrote:
100
Thanks a lot mate for posting this simple and sweet solution. Cheers Pete!

On 09/20/2006 at 5:56:42 AM MDT Vijay wrote:
101
Hi, I have the below solution based on Index and 3 column criteria. Thanks for the ideas i picked from here.

INSERT INTO RMW_PH_CrTransactionLog ( [ARNOC], [Transactiondate], [Transactionnumber] ) SELECT [ARNOC], [TrDt], [TrNo] FROM RMW_PH_Temp_Trans_Table AS a WHERE IndexNo NOT IN (SELECT IndexNo FROM RMW_PH_Temp_Trans_Table b WHERE IndexNo < (SELECT MAX(IndexNo) FROM RMW_PH_Temp_Trans_Table c WHERE Convert(BIGINT,b.ARNOC) = Convert(BIGINT,c.ARNOC) AND b.TrNo= c.TrNo AND b.ProdId = c.ProdId AND b.POSNo = c.POSNo)) AND UniqRec <> '2'

On 10/12/2006 at 7:16:07 AM MDT Jasmine wrote:
102
I need to add something to my query to allows me to check for duplicates that are similar, not identical, so that I know how many records are similar (within the other parameters of the query). For example: the first 3-5 characters are the same - Smith, J. Smith, John and Smith, John B. Also need to still pull any number of characters after the specified ones. I am new to SQL so may not be seeing something obvious. Any help?

On 10/20/2006 at 9:19:47 AM MDT need4speed wrote:
103
Gday from Sydney.

Here's my dilema.

Got a database. Field_A and Field_B. Simple... U think?

Here's some sample data:

Field_A Field_B shop1 apple shop2 orange shop2 banana shop1 nun-chucks shop1 pears

My query is something like this:

Select * from table where field_a like some_keyword or field_b like some_keyword

See, I wanna be able to search the DB by shopname OR by what it sells and return a single record for each shop that has the same name. I dont want it to return duplicate rows as it does when I search for shopname...

Thats sort of confusing so to illustrate. Here's some data... Just say my some_keyword was "shop1".

I get:

shop1 apples shop1 pears shop1 nun chucks

Now, for my own purposes, I dont care about the final two results... ie. I dont want to list any duplicates in field_a for my results....

anyone?

On 10/25/2006 at 4:45:33 AM MDT Stan Daymond wrote:
104
With all the talk going on here I don't see anybody providing good generic answer to the question.

If you have a key based on multiple columns, the correct statement is:

SELECT ColKey1, ColKey2, ... , COUNT(*) FROM TableName GROUP BY ColKey1, ColKey2, ... HAVING COUNT(*) > 1

This will return rows having duplicates.

Regards, Stan Daymond, London, UK

On 11/06/2006 at 6:48:07 AM MST Mohammed Rafi.A.S. wrote:
105
select * from table1 a where rowid>(select min(rowid) from table2 b where a.col1=b.col1 and a.col2=b.col2...)

On 11/06/2006 at 6:54:17 AM MST Mohammed Rafi.A.S. wrote:
106
select * from table1 a where rowid>(select min(rowid) from table2 b where a.col1=b.col1 and a.col2=b.col2...)

On 11/08/2006 at 1:10:14 AM MST bek wrote:
107
Hi,

I am trying to find duplicates in a field with different 'codes'. That part is easy, but the sql search should not care about the second character in the code.

So in my case 10AB56C0004 and 11AB56C0004 are each others duplicates.

Thanks in advance!

On 11/18/2006 at 1:36:26 AM MST Avie wrote:
108
This post helped a lot and worked great. Thanks.

On 11/20/2006 at 11:31:50 AM MST Stan wrote:
109
SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 )

If you want to exclude the combination of fields which have duplicates, extend it like this:

SELECT email + address COUNT(email + address) AS NumOccurrences FROM users GROUP BY email + address HAVING ( COUNT(email + address) > 1 )

On 01/07/2007 at 6:38:17 PM MST Daniel wrote:
110
Bek: You probably want to find select LEFT(code, 1) + RIGHT(code, Length(code)-2), count(LEFT(code, 1) + RIGHT(code, Length(code)-2)) from table group by LEFT(code, 1) + RIGHT(code, Length(code)-2) having count(LEFT(code, 1) + RIGHT(code, Length(code)-2)) > 1

On 01/09/2007 at 10:31:42 PM MST arun wrote:
111
How to find a duplicate records of a table where recordno and course_code in a table

On 01/17/2007 at 8:51:24 AM MST Ross wrote:
112
Thanks guys, just what i was looking for.

On 02/27/2007 at 2:18:32 AM MST sikruti wrote:
113
hi how can i filter out the duplicate values present in my table depending on not just with 1 column name but multiple column names . my tables has some 7 columns manes and i want to search n find out the duplicate rows depending on a combination of all the columnnames

On 03/01/2007 at 7:29:20 AM MST Noel wrote:
114
here's an example of a script that will find duplicates and delete them. It looks at 3 fields to determine if it is a duplcated record or not and it is also dependent on the record having an id field.

DELETE FROM SalesAddress WHERE (dwAddressId NOT IN (SELECT TOP 100 PERCENT MIN(dwAddressId) AS ID FROM SalesAddress GROUP BY szName, szPostCode, szAddress1 HAVING (COUNT(szName) > 0) ORDER BY COUNT(szName)))

Hope that helps. Noel.

On 03/19/2007 at 11:13:30 PM MST Avijt Pramanik wrote:
115
The Newsletter table contains two fields.

1. Name 2. Email

And we have to find out the common email with their all fields value.

So this should be a solution

select n.* from newsletter n where (select count(email) from newsletter where email=n.email)>1

On 03/23/2007 at 9:21:06 PM MST Paul N wrote:
116
This is a great thread. I've been working on this for a while now. I need to combine the 2 following queries. Basically, I need to pull only certan dates first, and then count and order. Would I have to create a tmp table first, in order to perform the 2nd query.

---------------- DECLARE @datetemp varchar(30) DECLARE @startdate varchar(30) DECLARE @enddate varchar(30)

SET @datetemp = (SELECT CONVERT(char(11), CURRENT_TIMESTAMP, 120)) SET @startdate =@datetemp + ' 00:00:00' SET @enddate = @datetemp + ' 23:59:59'

Select TimeGenerated,LogonUserName,UserWorkstation from SecurityLog Where (TimeGenerated>=@startdate and TimeGenerated<=@enddate and LogonUsername!=') ORDER BY LogonUserName Asc ---------------- SELECT LogonUserName, COUNT (LogonUserName) AS count FROM SecurityLog GROUP BY LogonUserName HAVING ( COUNT (LogonUserName) > 10) ORDER BY count DESC, LogonUserName ----------------

On 03/23/2007 at 9:22:53 PM MST Paul N wrote:
117
Trying to make it neater

DECLARE @datetemp varchar(30) DECLARE @startdate varchar(30) DECLARE @enddate varchar(30)

SET @datetemp = (SELECT CONVERT(char(11), CURRENT_TIMESTAMP, 120)) SET @startdate =@datetemp + ' 00:00:00' SET @enddate = @datetemp + ' 23:59:59'

Select TimeGenerated,LogonUserName,UserWorkstation from SecurityLog Where (TimeGenerated>=@startdate and TimeGenerated<=@enddate and LogonUsername!=')

SELECT LogonUserName, COUNT (LogonUserName) AS count FROM SecurityLog GROUP BY LogonUserName HAVING ( COUNT (LogonUserName) > 10) ORDER BY count DESC, LogonUserName

On 04/04/2007 at 3:40:31 AM MDT Jon wrote:
118
The mother of all queries. Ive been looking for something like this since the beginning of my programming career. Kudos!

On 04/10/2007 at 8:10:20 PM MDT Adam wrote:
119
You can also use 'select distinct' as shown below: SELECT distinct email FROM users

On 04/14/2007 at 7:52:06 PM MDT Nitin Asati wrote:
120
Hi, I want to search the duplicate records but there should not be any case consideration like if I have userid = 'nasati' and NASATI then it should give me count = 2 for this field?

On 04/24/2007 at 4:11:19 AM MDT Jeevan wrote:
121
Hi, I have two columns in a table A (First, Second) and wants to copy first column data in to second column and vice versa for every execution of query.

On 05/06/2007 at 9:55:46 PM MDT Rajini wrote:
122
I was told to work on one logic which is used to find dup values from 2 tables. Actually, this is happening through package. But my TL asked me to test the particular logic whether it is working or not. Once the testing is done, the package would go for deployment. I can find the dup's values from a table using simple sql. But, i don't know how to use the same concept here to test the logic. The logic is simply 3 select statements using the word EXISTS. Can any one help me? Let me know if you need the same logic to be poted.

On 05/14/2007 at 5:04:33 PM MDT Srikanth wrote:
123
Hi,

Please help me with this.

I have a table with 4 colmuns: A, B, C, D. The table has maybe around 5 rows in which two rows have exactly the same values in all the 4 columns. What is the SQL with which I can pickup the duplicate row?

Thx, Srikanth

On 05/17/2007 at 3:31:34 PM MDT AM wrote:
124
how to write output of a sql query to a csv file ?

On 05/17/2007 at 3:31:39 PM MDT AM wrote:
125
how to write output of a sql query to a csv file ?

On 05/25/2007 at 12:17:01 AM MDT Sellapillai N wrote:
126
--To delete duplicate rows begin tran select distinct * into #dms_t_reservation_sarms_temp from dms_t_reservation_sarms(nolock)

delete from dms_t_reservation_sarms

insert into dms_t_reservation_sarms select * from #dms_t_reservation_sarms_temp

DELETE FROM #dms_t_reservation_sarms_temp

select * from dms_t_reservation_sarms(nolock) select * from #dms_t_reservation_sarms_temp(nolock) rollback tran

On 08/07/2007 at 7:27:38 AM MDT Brian wrote:
127
Just a thank you for this. Saved me a few minutes :)

On 08/09/2007 at 2:31:57 PM MDT Eric wrote:
128
I have a table with the Columns: IFILN, IBOOKN, IDTBOK, inmate_id, visit_no.

inmate_id is a unique identifier attached to each person abd I need to be able to count the number of times that person appears and put that count into visit_no. example: inmate_id visit_no A 1 B 1 A 2 C 1 A 3 B 2

so that it shows this was the 1st visit, this was the 2nd etc. Can i use a form of this, if so/not any ideas on how to implement?

On 08/22/2007 at 3:07:04 AM MDT Nandoo wrote:
129
Its really good one... Keep it up.

On 08/22/2007 at 1:59:36 PM MDT Daniel wrote:
130
I have the following column: A, B, C and data for column A and B is duplicated, and need to remove the duplicated records but before I remove the record i have to check column 'B' which has some condtion if column 'B' data is 0 I have to remove all the other data which is duplicated either wise I have o keep the records, which means the first priority is for to get 0.

Example: A B C

1 3 1

On 08/22/2007 at 2:01:04 PM MDT Daniel wrote:
131
I have the following column: A, B, C and data for column A and B is duplicated, and need to remove the duplicated records but before I remove the record i have to check column 'B' which has some condtion if column 'B' data is 0 I have to remove all the other data which is duplicated either wise I have o keep the records, which means the first priority is for to get 0.

Example: A B C

1 3 0 1 3 2

I need to have the result of

A B C 1 3 0

Please any idea?

Thanks, Daniel

On 08/23/2007 at 8:49:28 AM MDT Shrey wrote:
132
Hi All: I have a similar issue -- let's say I have two tables, A and B, with two columns in each, table A is a table where users upload data, and B is a final table. A has duplicate data, as well as updated records. I've been trying to figure out a sql query which would go through table A, find which entries are not in table B and then copy them over. Further, it should check to see if any records have been updated in Table A and replace the data in Table B with the new data.

Any ideas?

example:

A(Temp Table)

1 1 2 22 3 3

B(Final Table) (Before addition of A) 1 1 2 2 4 4

B(Final Table) (After addition of A) 1 1 2 22 3 3 4 4

On 08/23/2007 at 5:50:02 PM MDT Ana wrote:
133
delete from table

That should do the trick...

On 09/03/2007 at 8:00:15 AM MDT John wrote:
134
I have a table with property addresses and dates sold. A property appears multiple times in the table, once for each time it is sold. I want a query that finds the latest date a property was sold.

On 10/12/2007 at 12:07:00 AM MDT Sinoy Xavier wrote:
135
If two rows having duplicates, then there is an easy solution to find it out. Suppose, trading_id and trading_name are the columns, which contain duplicates of marketer table, then,

SELECT trading_id,trading_name FROM schema.marketer_tbl a WHERE ROWID > (SELECT MIN (ROWID) FROM schema.marketer_tbl b WHERE b.trading_id = a.trading_id AND b.trading_name= a.trading_name);

And if we'r putting DISTINCT after the first select, we'll get the exact columns who are repeating.

Thanks, Sinoy Xavier Infosys, Bangalore

On 11/08/2007 at 4:42:04 PM MST Bobby wrote:
136
I have a table with a column of team names that are duplicated. I need to fill a combo box with these name but not the duplicates. How can I do this?

On 11/14/2007 at 6:42:27 AM MST Lakshmi wrote:
137
I have two tables A,B.Fields in A include userid, name and Fields in B include userid, groupname.

I want to select users who are belonging to more than one group.

On 11/18/2007 at 7:57:18 AM MST nisha wrote:
138
I have this requirement:

one employee table having emp details with salary. I need the result in such a way that all the rows with salary field repeated more than 4 times should come with all the other fields (empid, empname, salary) like tbl structure is 1 beena 10000 2 feroz 5000 3 joseph 10000 4 kiran 4000 5 giri 6000 6 geeta 4000

result should be 1 beena 10000 3 joseph 10000 4 kiran 4000 6 geeta 4000

Thanks in advance

On 11/26/2007 at 7:23:02 AM MST Mike wrote:
139
Hi,

I am looking to display all duplicate records in my table but in two fields.

tried this but it's not working. Could anyone tell me what's wrong with it?

SELECT NAME, Address1 COUNT(NAME) AS NumOccurName, COUNT(Address1) AS NumOccurAddress1, FROM general_table GROUP BY Address1 HAVING ( COUNT(NAME) > 1 ) AND ( COUNT(Address1) > 1)

On 11/28/2007 at 8:04:20 AM MST Radek wrote:
140
This is awesome solution.

On 11/28/2007 at 10:19:15 PM MST Sinoy Xavier wrote:
141
Hi Mike, just see the comment written by me on 10/12/2007. Hope this will help you.

On 12/03/2007 at 6:52:48 PM MST sathya wrote:
142
HI I have one doubt..I have list in which some duplicates are there.. I just want to retrieve only datas which is repeated(i need only duplicated values). for exmp if ram, guru , michel is repeated then i need those three alone.. how to solve it?

On 12/04/2007 at 10:43:55 PM MST suraj wrote:
143
Thanks guys, Great solution

On 12/12/2007 at 11:12:26 PM MST MNSK wrote:
144
How to find out duplicate in the same field? Like John John.. Thanks in advance

On 12/16/2007 at 10:40:25 PM MST markink wrote:
145
Simple and Great solution

On 12/24/2007 at 9:53:19 PM MST suman wrote:
146
how can write a query for that displays all the names which contains first and last alplhabets are same

On 12/26/2007 at 11:35:33 AM MST Gurus wrote:
147
sir, i want latest (nov,dec) OCA Dumps

please advice

On 01/08/2008 at 10:31:58 AM MST Akila wrote:
148
Hi,

I need to filter the records based on the unique combination 3 fields eg: in source fld1 fld2 fld3 a 1 1 a 1 1 dup record a 2 1 x x 1 x x 1 dup record

i need filter the duplicates so my output should be fld1 fld2 fld3 a 1 1 a 2 1 x x 1 so i need a query to get this output, i need to get the first occurance of the unique record.

pls hlp me thanks in advance

On 01/08/2008 at 6:54:32 PM MST steve wrote:
149
How can I count many times a character appears in a field. Let's say a filed contains the word apple. i want to count how many "p" appears in a field using SQL.

On 01/12/2008 at 6:38:39 AM MST ... wrote:
150
My god.. still no one can post a comprehensible deletion string for duplicated WITHOUT dropping everything around in how-ever-many tables?

My database is over 2GB in size... and MySQL is so non-efficient that exporting and importing such a thing would take over a month... *sigh*

On 01/18/2008 at 12:20:42 AM MST ron wrote:
151
hi, i have a problem similar to this,, i have duplicated rows in my db and i need to select nonduplicates (*) and only 1 from the duplicates .. I have been using this... SELECT transfer_id ,date, COUNT(transfer_id ) As NumOccurrences From my_table GROUP BY date, transfer_id HAVING ( COUNT(transfer_id ) > 1 ) order by date asc, transfer_id asc **** but it returns only duplicates... Does anybody have a solution to get both singles and duplicates ONLY ONCE

On 01/27/2008 at 11:27:33 AM MST Anonymous wrote:
152
My tool:

MS Access 2000 and later

A table with 5 columns (fields) and 500 records (rows)

What I know:

There are many dups in the 'dbfield3' field in my 'dbname' database.

What I want:

I want to see every single instance of the dup, descending from biggest dollar amount to smallest dollar amount (or if your field is populated by alpha characters such as city; Z to A).

EG. SQL Code:

SELECT dbname.dbfield1, dbname.dbfield2, dbname.dbfield3, dbname.dbfield4, dbname.dbfield5 FROM dbname WHERE (((dbname.dbfield3) In (SELECT [dbfield3] FROM [dbname] As Tmp GROUP BY [dbfield3] HAVING Count(*)>1 ))) ORDER BY dbname.dbfield3 DESC;

If you want to see all occurances (records) of the Dups - not just the first (or one of them) - and you are using MS Access 2000 and later; you will need to SELECT <every single field in your db>.

You WILL NOT see all dup records unless you SELECT <every single field in your db>.

This is an MS Access problem, which some people have been very clever to commercialize a solution for. Using the example above, you can easily create solutions or workarounds for similar MS Access shortcomings. Pure SQL does not have this issue and the original solution posted by pete above is simple and perfect.

Note:

A field is the same thing as a colunm or attribute; it's where your dup is hiding

A row is the same things as a record; which is where the dup is hiding

You have to understand that in order to find the dup (or anything in a db) you need the coordinates for it; ie. row 4, colunm 6 AND row 429, column 6. The two records share the same value for column 6; in record (row) 4 and record (row) 429.

On 01/30/2008 at 4:44:44 PM MST serj wrote:
153
The statement above works for me, but how do I merge the cells that It found, and uses the SUM of the values in the other two cells.

Thank you. Serj

On 02/06/2008 at 12:13:16 AM MST Kannan P wrote:
154
thank you so much...

On 02/09/2008 at 12:03:22 AM MST Amol wrote:
155
I appreciate your approach at this article .I really liked your tutorial for making a harder issue very simple. All The Best!

On 02/09/2008 at 12:03:39 AM MST Amol wrote:
156
I appreciate your approach at this article .I really liked your tutorial for making a harder issue very simple. All The Best!

On 03/02/2008 at 9:10:42 PM MST hamy wrote:
157
select count(distinct field1,[field2,...]) from table_one

works a little faster and simpler

On 03/04/2008 at 9:10:40 AM MST Vardark wrote:
158
$lol = mysql_query("SELECT 'ip' FROM 'brukere' GROUP BY 'ip' HAVING ( COUNT('ip') = 1) "); $kul = mysql_query($lol); ______________________________________

It does'nt work:( Why?

On 03/05/2008 at 12:05:12 PM MST renker wrote:
159
I need to find the duplicates in a selected date, but still comparing the the date selected rows with rows in other dates

How would i do that?

On 03/05/2008 at 12:14:55 PM MST Need help wrote:
160
Dear Programmers,

Thank you for your very good solutions. But wot if you hav 2 columns like this ---------------------- Col A | Col B | ---------------------- A B B A A C

Now I need my Output to be like This: A B Or,May be: A B A C

Wot would be the solution then?

On 03/09/2008 at 10:25:12 PM MST Anonymous wrote:
161
Hello All, This page has really helped me a lot Cheers

On 03/12/2008 at 8:34:17 AM MST cursorblock wrote:
162
Great, simple yet effective code. Helped save me a lot of time. Thanks.

On 03/19/2008 at 9:39:39 PM MST Yedhu wrote:
163
Hi, I want a mysqlquery to find the count of duplicated field values in the table, for example if value 'mohan' is repeating 4 times in the field ,the count should come as four ,i need a query fr this

On 04/07/2008 at 11:31:10 PM MDT mayank wrote:
164
i want to select the values in a column (c3) and copy them across to another column (c1) in the same table (say t1) with their counts concatenated for e.g.

orginal table (t1) c1 c2 c3 -------------- null joe 100 null tom 200 null tim 100

resultant table should look like: c1 c2 c3 ------------------- 100-1 joe 100 200-1 tom 200 100-2 tim 100

On 04/23/2008 at 1:52:32 PM MDT Sander wrote:
165
for me, the query didnt work (mysql 5.1)

This, based on the query above, does work:

SELECT COUNT(email), name AS NumOccurrences FROM user GROUP BY email HAVING ( COUNT(email) > 1 )"

On 04/28/2008 at 2:10:10 PM MDT bleh wrote:
166
Do you all get partial credit for working out the answers to these kids' homework assignments?

On 05/08/2008 at 8:30:27 AM MDT JOE wrote:
167
I don't think this thread answers a fundamental question: how does one create a sql statement that returns only the "count" of the number of records that are duplicates? For example: SELECT column1, column2 FROM Table1 GROUP BY column1, column2 HAVING COUNT(*) > 1 Yes, it selects the duplicate records, but i need to know HOW MANY are duplicates; SELECT @@ROWCOUNT right after the first SELECT ? There must be a better way.

On 05/08/2008 at 10:11:16 AM MDT Anonymous wrote:
168
JOE, there are many ways to find the number of records that are duplicates, unfortunately, non of them are pretty. here is one: select sum(count)-count(*) from (select count(*) from dupTable group by col1, col2 having count(*)>1 ) as foo; You are probably better off doing this programmatically ;)

On 05/08/2008 at 12:41:26 PM MDT JOE wrote:
169
Thanks for the reply. I tried this code and i get an error: Msg 8155, Level 16, State 2, Line 1 No column was specified for column 1 of 'foo'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'count'. i think i also need to mention that column1 and column2 in my example are not numeric fields, but strings (so SUM might not be the right appender.) I'm just looking for the total count of duplicate strings.




  



Spell Checker by Foundeo





Subscribe to my RSS Feed: solosub RSS
Tags