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

375 people found this page useful, what do you think?

Trackbacks

Trackback Address: 169/3EECA31943FD659BFE62CDC156DBABFD

Comments

On 10/07/2004 at 5:26:47 AM EDT Anonymous wrote:
1
select distinct(email) from users

This works little quicker than your solution.

On 10/07/2004 at 5:09:50 PM EDT 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 11/19/2004 at 8:25:20 PM EST Emanuel wrote:
3
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 05/05/2005 at 7:55:39 PM EDT Pradeep Chalise wrote:
4
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 6:30:58 PM EDT John wrote:
5
Ummm...

DELETE FROM A WHERE A.W <> +

???

On 05/10/2005 at 6:34:25 PM EDT John wrote:
6
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 11:46:14 AM EDT LB wrote:
7
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 10:42:29 AM EDT Artie wrote:
8
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 11:45:32 AM EDT Emanuel Costa wrote:
9
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 08/23/2005 at 7:37:07 PM EDT murali wrote:
10
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 8:25:13 PM EDT David wrote:
11
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 9:39:30 AM EDT Wayne wrote:
12
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 5:30:59 PM EDT Klyve wrote:
13
I cannot tell you how long I have spent looking for an answer to this!

Many thanks all

Klyve Dawson

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

On 10/19/2005 at 1:49:06 PM EDT Emanuel Costa wrote:
15
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 7:42:01 PM EDT Mooky Desai wrote:
16
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 3:26:31 PM EDT Frustrated wrote:
17
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/10/2005 at 6:20:33 PM EST Carl wrote:
18
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 6:45:54 PM EST Anonymous wrote:
19
Hi try this!

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

On 11/15/2005 at 2:55:51 AM EST Mar11b wrote:
20
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 10:27:17 AM EST Seb wrote:
21
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 12:12:32 PM EST shri wrote:
22
How can i find out same row in sql table? Pls give me the query for tht. Thanks.

On 11/24/2005 at 1:42:13 PM EST Sangeetha K wrote:
23
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 7:32:47 AM EST sam wrote:
24
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 9:56:36 AM EST Justin wrote:
25
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 7:16:59 AM EST Baljeet Kiroriwal wrote:
26
Great solution for fetching multiple records.

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

On 12/07/2005 at 9:53:53 AM EST jimesh wrote:
28
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 3:21:57 AM EST Mohit Dadu wrote:
29
Good I like it . Mogambo kush hua !! Ha Ha Mohit

On 12/14/2005 at 1:16:29 AM EST PH wrote:
30
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 12:11:45 PM EST AJ wrote:
31
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 3:51:30 AM EST Mark wrote:
32
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 8:37:18 AM EST Nailesh wrote:
33
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 9:16:42 PM EST amir wrote:
34
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 3:29:16 AM EST Arvind Singh wrote:
35
I want to select distinct record from any table . Which is the best method. (According to Performance)

On 01/16/2006 at 3:04:17 PM EST Brian Moore wrote:
36
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 6:47:29 AM EST Anonymous wrote:
37
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 9:33:37 AM EST David Adedeji wrote:
38
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 3:19:36 PM EST Anonymous wrote:
39
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 3:28:25 PM EST david wrote:
40
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 11:00:18 PM EST SG wrote:
41
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 11:26:37 AM EST Anonymous wrote:
42
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 3:37:19 PM EST Anonymous wrote:
43
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/09/2006 at 11:31:21 AM EST lalith wrote:
44
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 3:46:50 PM EST chat wrote:
45
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 10:51:25 PM EST Mark wrote:
46
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 6:39:24 PM EST Walsh wrote:
47
Thanks a lot!

On 03/07/2006 at 2:00:50 AM EST aya wrote:
48
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 9:23:38 PM EST Steve wrote:
49
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/08/2006 at 12:52:03 AM EST http://quadricit.co.nr wrote:
50
Need Help. I am backing up one table to another but I only want to copy the data

On 03/08/2006 at 10:49:12 PM EST sudha wrote:
51
The First one is working fine. Thank you

On 03/08/2006 at 11:03:25 PM EST Anonymous wrote:
52
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 11:03:43 PM EST sudha wrote:
53
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 8:11:10 AM EST Struggling New User wrote:
54
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 8:31:03 AM EST Miller wrote:
55
Struggling New User: Try select distinct count(part_number), part_number from part_number_table group by part_number;

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

On 03/13/2006 at 9:50:06 AM EST Miller wrote:
57
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 6:18:30 AM EST Miller wrote:
58
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 9:05:35 AM EST Miller wrote:
59
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 2:18:22 AM EST Mark wrote:
60
Miller - Thanks! That worked great! Thanks again

On 03/20/2006 at 8:48:14 AM EST wiley wrote:
61
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 9:22:30 AM EST learn sql wrote:
62
Damn.. such basic questions.... RTFM!

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

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

On 03/21/2006 at 11:37:32 AM EST Brandon wrote:
64
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 3:49:39 AM EST Akhil wrote:
65
how to delete the same record from same column and rowid2

On 03/23/2006 at 7:42:34 PM EST Assistance wrote:
66
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 2:56:00 PM EST Yo Man wrote:
67
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 3:38:25 PM EDT Jake wrote:
68
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 3:56:53 PM EDT Milelr wrote:
69
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 3:33:45 PM EDT Pvedi wrote:
70
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 7:16:05 AM EDT prashant wrote:
71
How to find out column name of the table by passing only field number to the procedure.

On 04/17/2006 at 9:11:17 AM EDT Miller wrote:
72
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/19/2006 at 1:27:18 AM EDT pdelaurentis wrote:
73
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 05/11/2006 at 11:50:28 AM EDT Walt wrote:
74
Worked for me except I needed to check a Unigueidentifier field and SQL will not allow a count on that data type.

On 05/16/2006 at 12:28:16 AM EDT Vasily wrote:
75
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 11:56:06 AM EDT jj wrote:
76
Great Solution. I was looking for this, just typed it in google and saw your page. Thanks.

On 06/08/2006 at 8:56:18 PM EDT Sujit wrote:
77
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 2:37:35 AM EDT PH wrote:
78
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 10:01:28 PM EDT tbsdy wrote:
79
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 6:51:29 AM EDT Ranjeet Kumar Bhatia wrote:
80
count how many records in two tables and return sum of both tables

On 07/10/2006 at 10:23:28 AM EDT bogdan wrote:
81
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 8:49:14 AM EDT Vijay wrote:
82
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 11:46:24 AM EDT Kevin wrote:
83
Thank you for this SQL snippet! You really saved me some serious time :)

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

On 09/20/2006 at 7:56:42 AM EDT Vijay wrote:
85
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 9:16:07 AM EDT Jasmine wrote:
86
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 11:19:47 AM EDT need4speed wrote:
87
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 6:45:33 AM EDT Stan Daymond wrote:
88
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 8:48:07 AM EST Mohammed Rafi.A.S. wrote:
89
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 8:54:17 AM EST Mohammed Rafi.A.S. wrote:
90
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 3:10:14 AM EST bek wrote:
91
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 3:36:26 AM EST Avie wrote:
92
This post helped a lot and worked great. Thanks.

On 11/20/2006 at 1:31:50 PM EST Stan wrote:
93
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 8:38:17 PM EST Daniel wrote:
94
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/10/2007 at 12:31:42 AM EST arun wrote:
95
How to find a duplicate records of a table where recordno and course_code in a table

On 01/17/2007 at 10:51:24 AM EST Ross wrote:
96
Thanks guys, just what i was looking for.

On 02/27/2007 at 4:18:32 AM EST sikruti wrote:
97
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 9:29:20 AM EST Noel wrote:
98
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/20/2007 at 1:13:30 AM EDT Avijt Pramanik wrote:
99
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 11:21:06 PM EDT Paul N wrote:
100
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 11:22:53 PM EDT Paul N wrote:
101
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 5:40:31 AM EDT Jon wrote:
102
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 10:10:20 PM EDT Adam wrote:
103
You can also use 'select distinct' as shown below: SELECT distinct email FROM users

On 04/14/2007 at 9:52:06 PM EDT Nitin Asati wrote:
104
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 6:11:19 AM EDT Jeevan wrote:
105
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 11:55:46 PM EDT Rajini wrote:
106
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 7:04:33 PM EDT Srikanth wrote:
107
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 5:31:39 PM EDT AM wrote:
108
how to write output of a sql query to a csv file ?

On 05/25/2007 at 2:17:01 AM EDT Sellapillai N wrote:
109
--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 9:27:38 AM EDT Brian wrote:
110
Just a thank you for this. Saved me a few minutes :)

On 08/09/2007 at 4:31:57 PM EDT Eric wrote:
111
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 5:07:04 AM EDT Nandoo wrote:
112
Its really good one... Keep it up.

On 08/22/2007 at 4:01:04 PM EDT Daniel wrote:
113
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 10:49:28 AM EDT Shrey wrote:
114
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 7:50:02 PM EDT Ana wrote:
115
delete from table

That should do the trick...

On 09/03/2007 at 10:00:15 AM EDT John wrote:
116
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 2:07:00 AM EDT Sinoy Xavier wrote:
117
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 6:42:04 PM EST Bobby wrote:
118
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 8:42:27 AM EST Lakshmi wrote:
119
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 9:57:18 AM EST nisha wrote:
120
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 9:23:02 AM EST Mike wrote:
121
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 10:04:20 AM EST Radek wrote:
122
This is awesome solution.

On 11/29/2007 at 12:19:15 AM EST Sinoy Xavier wrote:
123
Hi Mike, just see the comment written by me on 10/12/2007. Hope this will help you.

On 12/03/2007 at 8:52:48 PM EST sathya wrote:
124
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/05/2007 at 12:43:55 AM EST suraj wrote:
125
Thanks guys, Great solution

On 12/13/2007 at 1:12:26 AM EST MNSK wrote:
126
How to find out duplicate in the same field? Like John John.. Thanks in advance

On 12/17/2007 at 12:40:25 AM EST markink wrote:
127
Simple and Great solution

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

On 12/26/2007 at 1:35:33 PM EST Gurus wrote:
129
sir, i want latest (nov,dec) OCA Dumps

please advice

On 01/08/2008 at 12:31:58 PM EST Akila wrote:
130
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 8:54:32 PM EST steve wrote:
131
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 8:38:39 AM EST ... wrote:
132
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 2:20:42 AM EST ron wrote:
133
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 1:27:33 PM EST Anonymous wrote:
134
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 6:44:44 PM EST serj wrote:
135
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 2:13:16 AM EST Kannan P wrote:
136
thank you so much...

On 02/09/2008 at 2:03:22 AM EST Amol wrote:
137
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 2:03:39 AM EST Amol wrote:
138
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 11:10:42 PM EST hamy wrote:
139
select count(distinct field1,[field2,...]) from table_one

works a little faster and simpler

On 03/04/2008 at 11:10:40 AM EST Vardark wrote:
140
$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 2:05:12 PM EST renker wrote:
141
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 2:14:55 PM EST Need help wrote:
142
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/10/2008 at 12:25:12 AM EDT Anonymous wrote:
143
Hello All, This page has really helped me a lot Cheers

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

On 03/19/2008 at 11:39:39 PM EDT Yedhu wrote:
145
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/08/2008 at 1:31:10 AM EDT mayank wrote:
146
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 3:52:32 PM EDT Sander wrote:
147
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 4:10:10 PM EDT bleh wrote:
148
Do you all get partial credit for working out the answers to these kids' homework assignments?

On 05/08/2008 at 10:30:27 AM EDT JOE wrote:
149
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 12:11:16 PM EDT Anonymous wrote:
150
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 2:41:26 PM EDT JOE wrote:
151
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.

On 05/18/2008 at 9:51:25 AM EDT Sud wrote:
152
I have 4 columns(col1,col2,col3,col4) in table, I know I have duplicate values in col2, I want to find all the duplicates in col2 and check to see if col1(generated via a sequence)has the highest value ie.latest. I am on Oracle can any one help with the sql?

On 05/21/2008 at 2:45:28 AM EDT Anirudh wrote:
153
i kind of tried this problem one possible solution is copying existing entries in a new table without the duplicate ones'

Table A : A B C D E F A A A A D D D D D D D D D

the new table will have only non-duplicate values

the following command worked in sql server 2005.

SELECT DISTINCT * INTO C FROM A

new table C : A B C D E F

On 05/22/2008 at 3:54:47 AM EDT Ycos wrote:
154
If u have a table, "table1" with 2 colums : "column1" and "id" with records like: 1 "NAME1" 2 "NAME1" 3 "NAME2" 4 "NAME2" 5 "NAME2" 6 "NAME3" and u want to eliminate duplicates for "column1"

This is a solution:

delete table1 where id in select (select top 1 id from table1 t2 where t1.column1=t2.column1) from table1 t1 group by column1 having count(*)>1

On 06/11/2008 at 4:15:38 AM EDT Hemanth wrote:
155
Good Solution. This helped me a lot.

On 06/25/2008 at 4:07:59 PM EDT V wrote:
156
Can someone help me with my problem? My table looks like this

CID FNAME MIN MAX COM A OP1 0 23 5 A OP1 24 35 2 A OP1 36 99 1

A OP2 0 23 5 A OP2 24 35 2 A OP2 36 99 0

A OP3 0 23 5 A OP3 24 35 2 A OP3 36 99 1

B OP1 0 23 9 B OP1 24 99 2

B OP2 0 23 9 B OP2 24 99 2

B OP3 0 23 7 B OP3 24 35 3 B OP3 36 99 1

The expected duplicate results i need are: A OP1 A OP3 B OP1 B OP2

On 07/12/2008 at 11:48:30 PM EDT SmarterThanAnonymous wrote:
157
"select distinct(email) from users This works little quicker than your solution." - I enjoyed the fact that this comment completely missed the point of the post.

On 07/17/2008 at 10:32:15 AM EDT Vikas wrote:
158
Please help me in finding which record has been inserted twice? I am working with a huge database having too many columns. I am not able to find which record has been inserted twice by mistake.

On 07/21/2008 at 6:13:44 AM EDT Bhushan wrote:
159
The solution given below is wrong.

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 07/31/2008 at 3:33:41 AM EDT riaz wrote:
160
Thanks for your solution , it works fine.

On 09/01/2008 at 11:55:41 PM EDT Louie wrote:
161
I tried below SQL and it worked! Thanks for all the suggestions here! Much appreciated the collective effort from you guys! ;D Keep posting and keep on helping each other!

SELECT [fieldname1], [fieldname2] || '/' || [fieldname3] || '/' || [fieldname4] as [any_fieldname], count([fieldname2]) as NumOccurfieldname2, count([fieldname3]) as NumOccurfieldname3, count([fieldname4]) as NumOccurfieldname4 FROM [tablename] WHERE [you can filter some fields here in order to get desired result] GROUP BY [fieldname1], [fieldname2], [fieldname3], [fieldname4] HAVING (count([fieldname2]) > 1) and (count([fieldname3]) >1) and (count([fieldname4]) >1)

On 09/12/2008 at 8:48:06 AM EDT Anonymous wrote:
162
how to find out the duplicate values on the table for more than one column

On 10/15/2008 at 2:13:59 AM EDT kskumar wrote:
163
Thank you for giving the answer

On 10/15/2008 at 4:30:21 AM EDT wirenews wrote:
164
Hi, how do i list all my salesman-customers pairs who made the largest number of transactions together, whereby the customer has never complain against the salesman? That is, i have a lot of pairs, but i want to select all sellers to be distinct with their most popular customer

On 10/16/2008 at 10:35:37 AM EDT carolt wrote:
165
This question was raised a while back: "Does anybody have a solution to get both singles and duplicates ONLY ONCE"

I think I found a way to do this, just tried out this query and it seems to do the job. Can anyone tell me if/why it would not always work correctly? I could not find this exact syntax in any manuals or elsewhere.

SELECT DISTINCT * FROM tblname

When I used this to dedupe a table with lots of duplicates, it returned exactly one instance of each distinct set of values. The table has 5 columns: staffid empid lastname firstname zipcode

On 10/30/2008 at 12:44:14 PM EDT Teresa wrote:
166
I used Emmanuel's solution, the fourth comment, and it works beautifully for many columns. Include all columns in your table and voila!

On 10/30/2008 at 2:28:08 PM EDT Gajendra wrote:
167
I am having column like status in a table A that is having values like a,b,c,d i need to take status based on the priority wise(a-2,b-3,c-1,d-4) eg: Emp status 1 a 1 b 1 c 2 b 2 c 3 d 3 b

in the above table output should be emp status 1 c 2 c 3 b

please help me out to get that above output thanks in advance

On 11/04/2008 at 7:51:53 AM EST padam wrote:
168
Hi, i want retrieve duplicate records from sql column like

a,b,c,d,a,b,e,f,e i want to all the value which is duplicate result is a,b,e if u know plz tell me the code

On 11/15/2008 at 12:45:54 PM EST Ed wrote:
169
Hi Padam. retrieve all columns from duplicate records like this: SELECT * from tbl where tbl.col in ( SELECT tbl.col FROM tbl GROUP BY tbl.col HAVING ( COUNT(*) > 1 )) order by tbl.col Ed

On 01/28/2009 at 9:17:27 PM EST KN wrote:
170
i need some help. am develope training calender.. so in table i have training title n date for training ( from jan to dec). i have some problem when i wanna view all the training. fo example . in table i got 3 data: 1. training abc with date january. 2. training def with date march and 3. training abc with date april. so here i got one same training (training abc) but with different date.. so i want grap all the data but i want view it like this:

training abc jan april training def march

any idea how to do that.. i dont want to repeat the same training....i just want view it as onne training buat have diffenret date.. TQ so much

On 02/09/2009 at 1:53:46 PM EST PublicHealthGuy wrote:
171
Does the code work with aliases? I get an error using this:

SELECT t.ClientID, t.StatusChange, t.StatusChangeDuration, t.VaccineHomeName, " & _ "COUNT(t.ClientID) AS CountClientID " & _ "FROM tblStatusChange AS t " & _ "WHERE t.StatusChange = 'pN' " & _ "HAVING ( COUNT(t.ClientID) > 1 );"

On 02/11/2009 at 12:46:16 PM EST dee wrote:
172
Great Solution, Thanks

On 02/25/2009 at 7:26:26 PM EST Anonymous wrote:
173
Great Solution! Thanks

On 03/06/2009 at 2:51:36 PM EST Terry Pearson wrote:
174
@Ed, thanks for your comment. That was exactly what I needed...

"Hi Padam. retrieve all columns from duplicate records like this: SELECT * from tbl where tbl.col in ( SELECT tbl.col FROM tbl GROUP BY tbl.col HAVING ( COUNT(*) > 1 )) order by tbl.col Ed"

On 04/13/2009 at 2:03:56 AM EDT Suriya wrote:
175
Hi, What will happen if the data like below. In the below structure, INDEX 1 and 2 are duplicate. In these case i need to identify any one of the index as a duplicate? NAME NAME_1 VALUE DATE INDEX SURI SE 275 13/12/2005 1 SURI SE 375 1 SURI SE 475 1 SURI SE 275 13/12/2005 2 SURI SE 375 2 SURI SE 475 2

On 05/08/2009 at 12:28:14 PM EDT Kavipriya wrote:
176
Dear friends,, I m new in sql how to create a table..

On 05/28/2009 at 2:45:03 PM EDT SQL Lion wrote:
177
Thank you so much for the valuable information on removing redundancy from a table. Please follow the link below to get more information on Deleting duplicate records from a table efficiently. http://www.sqllion.com/2009/05/delete-duplicate-records/

On 06/09/2009 at 7:32:11 AM EDT Confused Novice Developer wrote:
178
I have a list of duplicates that I am trying to mark the first occurence of the duplicates as active and the remaiing as inactive. does anyone know how I can do this?

On 07/21/2009 at 1:01:00 PM EDT Mike wrote:
179
This is for my customer database:

begin --begin program

declare @last_cust int declare @cur_cust int declare @cur_cust2 int declare @count int

set @count = 0 set @cur_cust = 1 set @cur_cust2 = 2 select @last_cust = MAX(cst_id) from customers

While @cur_cust < @last_cust begin IF EXISTS (select * from customers where cst_id = @cur_cust) begin While @cur_cust2 <= @last_cust begin IF EXISTS (select * from customers where cst_id = @cur_cust2) begin IF ((select cst_ph from customers where cst_id = @cur_cust) = (select cst_ph from customers where cst_id = @cur_cust2) AND (select cst_ph from customers where cst_id = @cur_cust) <> ') OR ((select cst_alt_ph from customers where cst_id = @cur_cust) = (select cst_alt_ph from customers where cst_id = @cur_cust2) AND (select cst_alt_ph from customers where cst_id = @cur_cust) <> ') OR ((select cst_ph from customers where cst_id = @cur_cust) = (select cst_alt_ph from customers where cst_id = @cur_cust2) AND (select cst_ph from customers where cst_id = @cur_cust) <> ') OR ((select cst_alt_ph from customers where cst_id = @cur_cust) = (select cst_ph from customers where cst_id = @cur_cust2) AND (select cst_alt_ph from customers where cst_id = @cur_cust) <> ') begin insert into dup_cust (dup_id) values (@cur_cust) insert into dup_cust (dup_id) values (@cur_cust2) end set @count = @count + 1 end --end IF EXISTS (select * from customers where cst_id = @cur_cust) set @cur_cust2 = @cur_cust2 + 1 end --end While @cur_cust2 <= @last_cust end --end IF EXISTS (select * from customers where cst_id = @cur_cust) set @cur_cust = @cur_cust + 1 set @cur_cust2 = @cur_cust + 1 print @cur_cust end --end While @cur_cust <= @last_cust

--insert into dup_cust (dup_id, dup_ln) --values (@tempers, ') print @count end --end program

On 08/06/2009 at 3:03:52 AM EDT Nripin wrote:
180
Thanks Mate !!!!

On 08/06/2009 at 3:04:04 AM EDT Nripin wrote:
181
Thanks Mate !!!!

On 09/16/2009 at 7:50:26 AM EDT Vignesh wrote:
182
I have two tables A and B

Now Table A has

Last Name, First Name, Country, New Table

Now Table B has Last Name, First Name, Country, Old Table

I want to remove dupilcates of the these 2 tables and have my result as a New Table C which has no duplicates.

i.e C= a-b

plz help me..

Plz

On 10/01/2009 at 4:35:05 PM EDT Anon wrote:
183
Vignesh, try this: create table table c as select last name, first name, country, new table from table a union select last name, first name, country,old table from table b;

This will give you all the unique records in a and b

On 10/07/2009 at 1:30:49 PM EDT Jessica wrote:
184
Thanks for this post, is simple and works great!

On 10/08/2009 at 7:25:38 PM EDT frustrated wrote:
185
so no one came up with a way to select *only* the duplicate rows?

On 10/09/2009 at 12:35:59 PM EDT frustrated wrote:
186
well here is one from delphifaq. Worked for me.

// return all pairs of city IDs that have the same city name

select c1.city_id, c2.city_id, c1.city_name from cities c1, cities c2 where c1.city_id < c2.city_id and c1.city_name = c2.city_name

// version for Micrsoft's MSSQL Server // make use of the HAVING clause select city_name from areas group by city_name having count(*) > 1

On 10/14/2009 at 7:59:16 PM EDT Anon wrote:
187
Frustrated, you could do the opposite of what I wrote for Vignesh which is:

create table table c as select last name, first name, country, new table from table a intersect select last name, first name, country,old table from table b;

This should give you only the commonalities of a and b.

On 11/12/2009 at 4:18:53 PM EST WhatThe wrote:
188
"The mother of all queries. Ive been looking for something like this since the beginning of my programming career. Kudos!"

Seriously? When was that, yesterday? Get a book.

On 11/18/2009 at 2:12:07 AM EST Lui wrote:
189
ok, there are a lot of excellent solutions here for finding duplicates,and they all great. I am new with SQL. Could someone please suggest one of the most commonly use solution ( I know there is many) for finding duplicate records from two tables. The results will show the duplicates. Thx

On 12/01/2009 at 4:25:24 AM EST Premila Devi wrote:
190
CREATE TABLE cam.cam_pollux_reconciliation_asset ( id bigserial NOT NULL, alternativeserialnumber character varying(255), category_desc character varying(128), description character varying(255), location_id bigint, metername character varying(255), metersubtype character varying(255), mixedmeter character varying(255), model_id bigint, panel_id bigint, quantity integer, serialnumber character varying(128), is_serialised boolean, source integer, unit_id bigint, alternative_serial_number character varying(255), code character varying(255), meter_name character varying(255), meter_sub_type character varying(255), mixed_meter character varying(255), serial_number character varying(128), secstatus character varying(10), status character varying(10), CONSTRAINT cam_pollux_reconciliation_asset_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); ALTER TABLE cam.cam_pollux_reconciliation_asset OWNER TO cam_it

remark: I have a serial_number that is representing duplicate like:

011-010-000002 011-010-000002

for the serial number..i did not want to delete/clean the table..pls advice me

On 12/01/2009 at 11:40:47 PM EST restroika wrote:
191
hai,, i get the case,, *. database = jobs1; *. tabel = barang; *. field = id_Barang, nabar; *. filed id_Barang, content = "12345","1234" can i look for data from field id_barang with only one query select?

On 12/04/2009 at 7:01:44 PM EST je wrote:
192
Is there a way to list just the set of duplicates from a table.

the solutions above list the records only once.

I have a table where the duplicate is based on 5 columns but the remaining column may be different. So I want to query and bring back only the dupes. I don't want a count.

On 12/07/2009 at 5:28:37 PM EST Gaurav wrote:
193
Hi,

I have a table A with fields name and email .One person can have more than two emails write a query which displays the names along with email only for those who have more than one email ids.

On 12/15/2009 at 6:21:59 AM EST Shamal wrote:
194
Hi, i have a problem in deletion query, i have a table with the following columns(Date OperatorName(A) (A)Number .....etc) how can i write a query that delete duplication in the table?any urgent help plzzzzzzzzzz?

On 12/15/2009 at 6:24:03 AM EST Shamal wrote:
195
Hi, i have a problem in deletion query, i have a table with the following columns(Date OperatorName(A) (A)Number .....etc) how can i write a query that delete duplication in the table?any urgent help plzzzzzzzzzz?

On 12/29/2009 at 9:24:30 AM EST Guest wrote:
196
Look at this desision "http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/"

On 12/29/2009 at 9:17:55 PM EST Ron wrote:
197
Great Work!

Works like a charm, and can be used in sub-queries

On 01/25/2010 at 1:21:35 PM EST Richard wrote:
198
Here's a good question:

I have a table with duplicate records, but the duplicate records are based on all fields with the exception of the key field. All records have an ID which is the pk, so technically, the records are unique, but I need to delete duplicate records based on the other fields.

Example:

ID Name Number City 1 John Doe Nashville 2 John Doe Nashville

I want to keep one of them and remove the other. Each has a unique pk, so selecting which records to delete is difficult. Doesn't matter which one is deleted, as long as only one remains.

Any help would be greatly appreciated!! Thank you so much!

On 02/02/2010 at 1:01:43 PM EST Jason wrote:
199
SELECT * FROM maintable p WHERE ((SELECT COUNT(id) FROM maintable WHERE p.columnwithdupes = maintable.columnwithdupes AND p.id <> maintable.id) > 0)

On 02/02/2010 at 10:34:52 PM EST Anonymous wrote:
200
cheers hopefully will help

On 02/02/2010 at 11:58:39 PM EST Anonymous wrote:
201
I want to write a sql query To display the customers first Name and last name when I send him a MSG which will display the list of Sent Messages in a page...Right Now it is displaying The MessageToID = '0'..Can u plz help..

On 02/03/2010 at 12:39:06 AM EST shweta wrote:
202
I want to write a sql query To display the customers first Name and last name when I send him a MSG which will display the list of Sent Messages in a page...Right Now it is displaying The MessageToID = '0'..Can u plz help..

On 02/10/2010 at 9:03:26 PM EST hanuman wrote:
203
i need to get distinct name of the person. but in my data table person is is there with persionID FullName 01245 Donkey kong 01245 Donkey kongKing both are same person's name now i can not get the distinct name out of it as the name stored differently , even though i use Distinct person ID. And joining with other datatables it gives me more bad results . I don't see any condition also which i can apply for this selection. Help

On 02/17/2010 at 2:05:12 PM EST Zeeshan Khan wrote:
204
If you are running an online website with heavy traffic, and you want to modify any index, for which you first have to drop the previous index and then add it. Then it is highly recommended to add the index first, with another index name, and then drop the previous index. For more details, check out:

http://www.webtechquery.com/index.php/2010/02/view-add-and-drop-indexes-in-mysql/

On 02/19/2010 at 4:14:35 AM EST Taichi wrote:
205
hmm i have another version of problem here is the example

user id date user1 101 2010-02-19 user1 102 2010-02-17 user2 103 2010-02-17

how can i display only one per user with recent date? so basically the output will be

user id date user1 101 2010-02-19 user3 103 2010-0217

On 02/22/2010 at 8:38:39 AM EST Ansari Mohammad Qayamuddin. H wrote:
206
You can use. SELECT * FROM [your_table_name] ORDER BY [your_table_name].[date] ASC

On 02/23/2010 at 12:56:26 AM EST dinesh wrote:
207
customer table have some duplicate records i want display duplicate records?can u give solutios anybody.

On 02/27/2010 at 2:57:26 PM EST kiran wrote:
208
I have two fields in a table which while retreiving i want to combine both and display output as one. for ex:empno,empname when retreived i want it to come as single field .Can anyone provide me sql query for this.

On 03/03/2010 at 7:12:43 PM EST helpAppreciated wrote:
209
I have a relation with N columns of the same type, and I have to write a query that returns the tuples having the max number of identical values, any suggestions?

On 03/16/2010 at 6:52:50 AM EDT FlexSharp wrote:
210
I have a table that looks like this:

|StreetName NR| NR | |Teststreet 34| 34 |

How can i delete only the number in Streetname when it is the same in NR??

On 03/18/2010 at 10:50:33 AM EDT Francois wrote:
211
Hi All I have to write a query that is driving me up the wall:

I have a table with 4 fields; |ID| Code|Qty|Status| |01| 1234|001| Q | |01| 2222|001| N | |01| RDSA|003| Q | |02| XXXX|010| N | |02| 1234|200| N | |03| BBBB|175| N | |03| 2222|001| Q |

I have to find all of the IDs that ONLY have records with a status of 'N'.

The required resultset should be: |02| XXXX|010| N | |02| 1234|200| N |

On 04/04/2010 at 5:09:24 PM EDT Mike wrote:
212
Hi Hope I have entered this in the correct place? If not apologies.

I have two tables, one of which is a "virtual" table for the want of a beter word, there are two elements that make the table. virtual_code( which holds the heading for the table & Virtual_code (Which holds the data). I am trying to compare data in the first table to return all the missing data from the second table.

Table1 Name Unit Column1 = date Column2 = Unit Column3 = base

Table2 Virtual_code = Zone Virtual Code Colums Virtual_code Unit Description

My attempt goes something like this

Select u.unit from unit u virtuasl_code v where u.unit not in((select Virtual_code from Virtual_name where virtual_name= 'ZONE' and virtual_code <> ' ')) and u.unit = v.virtual_code 0rder by virtual_code

It works, after a fashion but I am not 100% convinced my script is correct. Can anyone confirm I have it correct or suggest another way of doing it?

Rergards

Mike

On 04/13/2010 at 6:33:49 AM EDT yogesh wrote:
213
Sr.No. Ac_Year 1 2008-09 C201 2008-09 C202 2008-09 C204 2008-09 C203 2 2009-10 C301 2009-10 C303 i want to print table like this which query i need to run ... i am not much aware of oracle in deep..

On 04/13/2010 at 9:34:11 AM EDT simon wrote:
214
An easier way of finding duplicates is this:

SELECT col1, count(*) AS col3 FROM t1 GROUP BY col1 HAVING count(*) > 1

so in the case of an email it would search for the email. If it appears more than once, then the out put will be that email displayed once and the number of times it is repeated.

also @ Francois would this work?

Select * from table where Status = 'n'

On 04/13/2010 at 10:14:20 AM EDT Anonymous wrote:
215
Hi there @ Simon Thanks fir your reply.

Unfortunately it's not that simple:

When I posted last, the display of the records came out jumbled. I hope this time it will be a little more clear:

|ID| Code|Qty|Status|

|01| 1234|001| Q |

|01| 2222|001| N |

|01| RDSA|003| Q |

|02| XXXX|010| N |

|02| 1234|200| N |

|03| BBBB|175| N |

|03| 2222|001| Q |

So a select all with status = 'n' would include ID(1) and ID(3) as well.

The resultset that I require should only have ID (2), as all of the records with an ID of 2 has a status of 'n'

On 04/14/2010 at 8:05:11 PM EDT simon wrote:
216
@ Francois

Right i see. try

Select * from table where Status = 'n' AND id = 2

On 04/15/2010 at 5:37:30 AM EDT Francois wrote:
217
Hey, @Simon

No, a simple select statement will not do. The example that I have given only has three IDs, but might have millions in real life. I need a generic solution that will ONLY list IDs which have records that ONLY contain a status of 'n'. Any ID that has even one record with a different status MUST be excluded.

The way that I have done this is to use a cursor, first summing the number of entries for each ID, the secondly summing the number of entries for each ID that has a status of 'n', and then comparing the two results. If an ID has a "total record" count that equals the "'n' status record" count of x, it is included in the end result set. If the "'n' status" count is less that the "total" count, it is obvious that there are other statuses involved, and the ID is excluded from the result set.

The above works fine, but I am sure there must be a more elegant way of doing this. I just don't know how!

Kind regards Francois

On 04/15/2010 at 5:38:20 AM EDT Francois wrote:
218
Hey, @Simon

No, a simple select statement will not do. The example that I have given only has three IDs, but might have millions in real life. I need a generic solution that will ONLY list IDs which have records that ONLY contain a status of 'n'. Any ID that has even one record with a different status MUST be excluded.

The way that I have done this is to use a cursor, first summing the number of entries for each ID, the secondly summing the number of entries for each ID that has a status of 'n', and then comparing the two results. If an ID has a "total record" count that equals the "'n' status record" count of x, it is included in the end result set. If the "'n' status" count is less that the "total" count, it is obvious that there are other statuses involved, and the ID is excluded from the result set.

The above works fine, but I am sure there must be a more elegant way of doing this. I just don't know how!

Kind regards Francois

On 04/23/2010 at 5:21:37 AM EDT Anonymous wrote:
219
thanks for the solution

On 05/04/2010 at 7:19:16 PM EDT Trev wrote:
220
here's one that i'm scratching my head on, i have a table with only two columns, [sales contact], and [sent to customer date] - the sent to customer date field is not a true date field, its an nchar(30) and is in the format of 01-jan-10

i'm trying to distinctly count the number of entries by date by sales contact - this query works but only returns 1 column with the months i specify, i would like to iterate through all months:

select distinct [sales contact], COUNT([sent to customer date]) as 'jan' from quotes where [sent to customer date] like '%feb-10%' group by [Sales Contact]

and of course changing it to:

select distinct [sales contact], COUNT([sent to customer date]) as 'jan', COUNT([sent to customer date]) as 'feb' from quotes where [sent to customer date] like '%feb-10%' group by [Sales Contact]

adds the second column as feb, but fills it with the results from jan.

On 05/04/2010 at 7:21:28 PM EDT Trev wrote:
221
correction on my 'like' statement in the first query i said that worked above - should have been '%jan-10%' - but again, still only returns the count the specified month

On 06/04/2010 at 4:30:06 AM EDT KCM wrote:
222
I have some problem like this :

I have a table where data is duplicate.There are 3 fields like fname,mname,lname and the data entered is like this :

record 1 : fname=Kcm mname=null lname=K record 2 : fname=Kcm K mname=null lname=null

How to find out like this duplicate records ?

On 06/06/2010 at 1:45:15 PM EDT jyothi wrote:
223
I have a table like below, Formid FormDate company profile

In that FormDate contains Dates of the records formed.It contains the dates of Year 2008 to 201o and unique formid. I have created one web page in that From Date and To Date selection list is there. If I select the particular from date and To Date and if click submit button, It need to show the particular form id of the records according to the date. And when I click on to that Particular form id it need to show the particular record according to the particular form id.

On 06/14/2010 at 2:15:30 PM EDT msb wrote:
224
Thanks for above solution.

Here is another way to get the duplicate values in table; assuming that the field has a unique ID:

SELECT DISTINCT a.tablefield Table AS a INNER JOIN Table AS b ON a.tablefield = b.tablefield WHERE a.tablefieldID <> b.tablefieldID

Hope this helps too http://www.arabnet5.com

On 06/16/2010 at 6:25:20 PM EDT theflyingtaz wrote:
225
I am using SQL 2000 and I have a temp table that I have created with 13 columns joined from 4 tables. I need to delete any rows where column 1 is not unique and column 7 is null. So if I have ABC | NULL and ABC | Joe I need to delete the row that has ABC | Null. Any ideas?

On 06/22/2010 at 8:03:05 AM EDT Fanuel wrote:
226
How can i select duplicates having just compared part of characters in a roll, i tried this but its not working:

select National_id from db_country group by National_id having count(substr(National_id,1,5))>1;

On 06/22/2010 at 8:16:38 AM EDT Fanuel wrote:
227
How can i select duplicates having just compared part of characters in a row, i tried this but its not working:

select National_id from db_country group by National_id having count(substr(National_id,1,5))>1;

On 06/24/2010 at 9:29:31 AM EDT markw wrote:
228
Hi, just what I was looking for thanks.

Should I join the crowd with a comment on the lines of "I want an SQL statement that cleans my teeth, puts my babies to bed, and goes to the supermarket for me"?

I think not. ;)

On 06/27/2010 at 5:08:14 AM EDT sahar wrote:
229
I want to display for me the following output 1/11+11/22+22/33+33/44+44/55 by using for loop

On 06/28/2010 at 8:35:56 AM EDT Anonymous wrote:
230
Hi, I have a table with 8 fields id email neurology neuroscience pharmacology physiology psychiatry psychology

how many emails there are where the status = subscribe for more than one field

On 07/07/2010 at 12:15:31 PM EDT domenic wrote:
231
I am a bit confused. I have a table in a database with phone numbers, each phone number assigned to a specific account. Sometimes a phone number gets assigned to more than one account which is a problem. I need to list all DUPLICATE entries in the phone number table. How do I do this?

On 08/21/2010 at 9:37:35 PM EDT aa33030 wrote:
232
SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) ===========

Where do I go to do all of this?

On 08/21/2010 at 9:40:02 PM EDT aa33030 wrote:
233
Ok I got. You go to the query form, copy and paste what you've written

On 08/30/2010 at 6:10:37 AM EDT Aloo Paratha wrote:
234
select mobileno from table1 group by mobileno having count(*)>1

On 08/30/2010 at 6:14:19 AM EDT Aloo Paratha wrote:
235
Truncate table table1

then you need to drop the table. Thats all... You are done....and no need to delete the duplicate rows from the table. Good Luck Guys! Enjoy it!

On 09/03/2010 at 8:23:10 AM EDT Jagdeep Mankotia wrote:
236
I have a table with duplicate entries. I do not have PK or and ID. Now I want to find duplicates and delete them. Only one record will remain from the duplicates How can I do this process in sql?

Thanks in Advance Jagdeep Mankotia

On 09/08/2010 at 5:43:39 AM EDT chandu wrote:
237
Hi,I have wrote one query,it will give some set of records,from that result i need to fetch the first 3 records,anyone can please help me how to do this. my query is select SYSTEMCATEGORY.CATEGORYNAME, sum(ACCOUNTINGTRANSACTION.AMOUNT) amount from SYSTEMCATEGORY,ACCOUNTINGTRANSACTION where ACCOUNTINGTRANSACTION.CATEGORYID = SYSTEMCATEGORY.CATEGORYID and ACCOUNTINGTRANSACTION.TXNTYPE = 'DR'and ACCOUNTINGTRANSACTION.ARRANGEMENTID in (select ARRANGEMENT.ARRANGEMENTID from ARRANGEMENT where ARRANGEMENT.PARTYID in(select CUSTOMER.PARTYID from CUSTOMER where CUSTOMER.AGE between 25 and 32)) group by SYSTEMCATEGORY.CATEGORYNAME order by amount desc

On 09/20/2010 at 8:29:35 AM EDT Anonymous wrote:
238
dear i want to remove same values into column ,lets say col1 col2 col3 ali 123 1234d ali 123 4544 ali 123 4554

i want to handle in queries like this col1 col2 col3 ali 123 1234d 4544 4554

Any Soln will be highly appreciable.

On 09/20/2010 at 8:30:36 AM EDT Anonymous wrote:
239
dear i want to remove same values into column ,lets say col1 col2 col3 ali 123 1234d ali 123 4544 ali 123 4554

i want to handle in queries like this col1 col2 col3 ali 123 1234d 4544 4554

Any Soln will be highly appreciable.

On 09/20/2010 at 8:32:01 AM EDT Anonymous wrote:
240
dear i want to remove same values into column ,lets say col1 col2 col3 ali 123 1234d ali 123 4544 ali 123 4554

i want to handle in queries like this col1 col2 col3 ali 123 1234d 4544 4554

Any Soln will be highly appreciable.

On 09/23/2010 at 6:16:49 AM EDT Sarge wrote:
241
Simple and effective, works like a charm!

On 10/07/2010 at 5:19:53 AM EDT Daruru wrote:
242
Hi

I would like to know the DATABASE concepts. Could anyone can provide good URS's for DATABASE concepts, it would be grateful.

On 10/08/2010 at 9:05:35 PM EDT Albert wrote:
243
Hi, i have a big problem in filtering data. This is my table: col1,col2,col3 1111,aaaa,01 1111,bbbb,21 1111,cccc,09 2222,xxxx,0000 2222,cccc,09 3333,aaaa,01 3333,cccc,09

I want to see only rows that match a condition, example: col2=aaaa and col3=01 and col2=cccc and col3=09. If this condition is verified, the output will be: 1111 3333 Hope you can help. Thank you.

On 10/28/2010 at 11:20:16 AM EDT kiran wrote:
244
i want delete duplicate records not orginal records like i have table like this empno ename 100 kiran 100 sub 120 nar 120 kis after deleting records then it shows like empno ename 100 kiran 120 kis

On 10/28/2010 at 4:24:05 PM EDT Jake wrote:
245
I am looking for the following query.

I need to look for duplicate accounts, from the following Items. Notice the Items are in there a couple times and share some accounts. I need to remove the items if it shares the same account with another item.

Item Id Account Id 354715 274615 354715 274616 354716 274613 354716 274614 355266 274615 355266 274616 355267 274613 355267 274614

I look forward to your response.

Thank You! Jake

On 10/29/2010 at 10:50:21 AM EDT AbhayC wrote:
246
I have same issue like Jasmine above ( 10/12/2006 at 9:16:07 ). I have a customer table which has a column for customer_name. It has values like say,

John Thomas Kvamme Mark Thomas John john J Thomas Mark Kvamme Rob Volkman

and so on .. is there any way to group "similar" names together ??? I basically want to find out a way to list all these 'similar' names together and group by their count .. so something like

John Thomas - count=3 Mark Kvamme - count=2 Rob Volkman - count=1

On 11/01/2010 at 10:25:52 AM EDT vidhya wrote:
247
hi

There are two tables, in table 2 you have three columns break start time,break end time and employee id.there are 4 breaks.how to display the employee id no if its being repeated in all the breaks.

thanks in advance..vidhya

On 11/03/2010 at 10:30:59 AM EDT Anonymous wrote:
248
Hi I am tryng to get duplicate contact records based on email, last name and first 3 letters of the first name and I want to know which compnay they are attached to (So I am using two tables Compnay and Contact) Can anyone help?

===== This only give me dupes based on full name and email address ======== SELECT COUNT(*) AS no_of_duplicates, Email, Full_Name FROM dbo.Contact WHERE (Email IS NOT NULL) AND (Email <> ' ') AND (Email <> '.') GROUP BY Email, Full_Name, Inactive HAVING (COUNT(*) > 1) AND (Inactive = 0) ORDER BY Full_Name, no_of_duplicates DESC

On 11/06/2010 at 5:28:50 AM EDT Anonymous wrote:
249
Its simple Delete your table data and starts a new one! :D

On 11/08/2010 at 5:21:04 AM EST Santu wrote:
250
Hi all, I have trying to write a sybase query to check for duplicate records and to execute conditions when the number of duplicate records are more than 5, and also when the duplicate records is less than 5, So i have the below query, please guys provide a review and feedback on the below query

IF (select A,B,C from TEMP_TABLE group by A,B,C having count(*) > 1 ) > 5 Print "Error" else ((select A,B,C from TEMP_TABLE group by A,B,C having count(*) > 1) <5 INSERT INTO TABLE_1 (A,B,C) SELECT A,B,C FROM TEMP_TABLE )

On 11/08/2010 at 5:23:20 AM EST Santu wrote:
251
guys please help me with the above query i have just put on the blog, i have just started working on the Sybase DB and i am finding it difficult

On 11/10/2010 at 12:54:03 AM EST WAN wrote:
252
Hi guys, I have a case here: I have 2 table, say Header and Detail. Table Header contain rows: ID CNo A 1 B 2 C 3

Table Details contain rows: CNo PAY 1 Cash 1 VISA 2 CASH 3 CASH 3 MASTER 3 VCH

My Question is : how do i link the Header and Detail as 1 rows but new column will be created to hold each PAY name. such as reuslt will be: ID CNo PAY1 PAY2 PAY3 A 1 CASH VISA B 2 CASH C 3 CASH MASTER VCH

Please help me ms.sql master out there, how to do this and thanks in advance.

On 11/25/2010 at 5:56:43 AM EST Anonymous wrote:
253
thanks. it helped me..

On 12/14/2010 at 6:25:59 PM EST Anonymous wrote:
254
I have a question. I have a table with duplicate IDs because other columns are different. I want to get only one of the IDs. Distinct didn't work because of the other columns being different. How can I do this?

On 12/26/2010 at 5:37:04 AM EST Ashley wrote:
255
Hi I would like to find all duplicate entries that have the same idcode but different value

example

select * from table where field1 = field1 and field2 != field2

but I get errors

On 01/04/2011 at 11:15:59 AM EST Carlos wrote:
256
Hi I need a query to find the salary of all the employees in my company that earn more than me. Have no idea of the tables or data structure. Database is BD2. Thanks.

On 01/04/2011 at 11:16:24 AM EST Carlos wrote:
257
Hi I need a query to find the salary of all the employees in my company that earn more than me. Have no idea of the tables or data structure. Database is BD2. Thanks.

On 01/10/2011 at 10:16:55 AM EST ooliki wrote:
258
Hi am havin problem of duplication of records when i querry two tables with innerjoin clause (SELECT * FROM registration c INNER JOIN products p where c.id = p.validcode && p.productcategory = 'Others'). please any body that can assist

On 01/20/2011 at 3:05:15 AM EST ela wrote:
259
assume that one column has 10 values...sum has duplicates...how to find which value has maximum number of duplicates in sql

On 01/25/2011 at 4:28:55 PM EST alexa wrote:
260
Thank you so much! I used this query to show a client how terrible it is to pull records by name instead of ID. He got it. :D

On 01/28/2011 at 7:15:46 PM EST alexx wrote:
261
Awesome, my sql is so rusty after 10 months of Javascript/ActiveX, I could not think of how to do this anymore (and had to check for dups across 3 cols). THANKS EVERYONE! I know the date on the original post is 2004 but it goes to show old knowledge is still good :)

On 02/03/2011 at 7:56:44 AM EST hazari wrote:
262
thank you so much but i have some other query.i have a database having column name(name,type(dedicated,shared),startdate,enddate) i want to find duplication when a new entry is inserted if a name,type(dedicated),fromdate is enter and its already inserted in between a particular date then it show message that name cant be appoint as it already appoint on that day..

On 02/03/2011 at 8:57:38 AM EST Marcus wrote:
263
I have a similar problem to Trev's but more complex.

I have one table with sales data spawning over multiple years. This table includes all buying customers (CustNo) with sales month (Date) and shop area (ShopNo)

What I need is a result displaying a unique count of customers, starting in january and for each month adding the customers that haven't been buying yet in this year(firstbuyers). This will be calculated for each area too.

Example: In january 500 customers bought articles, in february it was 530, of which 40 where there for the first time this year, in march 490 customers bought articles, of which 25 were there for the first time this year.

Smaller figure will occur for individual areas. Example: In one sub area 350 customers bought something in january; 360 in february (15 firstbuyers) and 340 in march(20 firstbuyers).

My increasing counting table would then look like this:

Year:Month:Area:Count: 2010:January:AllAreas:500 2010:February:AllAreas:540 2010:march:AllAreas:565 2010:January:SubArea:350 2010:February:SubArea:365 2010:march:SubArea:385

How do I achieve that without creating multiple temp tables for each month and merging them manually, grouping and counting customers after each merger?

Thanks for your help!

On 02/07/2011 at 3:42:17 AM EST dipak wrote:
264
i have table with structure

id f1 f2 ----------------------- 1 a x 2 b y 1 a z

*id is not primary key

Query should return result like

id f1 f2 ------------------- 1 a x,z 2 b y

On 03/01/2011 at 2:02:06 PM EST Mohsin wrote:
265
I need some help i have two tables table(A)(one column) having 200k records and another other table(B)(one column) having 600k records i am trying to find duplicate records (i-e records that that are in both tables) also i need records that exist in table A and are not in table B.

On 03/04/2011 at 9:09:13 AM EST Pummy Manku wrote:
266
Mohsin, use this code

SELECT t1.field_name FROM tablename t1, tablename t2 WHERE t1.field_name=t2.field_name AND t1.rowid <> t2.rowid;

On 03/08/2011 at 1:59:49 PM EST Mohsin wrote:
267
Thanks Pummy Can u please explain the query ? and statement t1.rowid <> t2.rowid. Sorry i am new to SQL

However i used following query

select * from tb1 where field1.tb1 in (select field1.tb2 from tb2)

However this query took 11 hours to complete

On 03/25/2011 at 8:00:46 AM EDT HITESH wrote:
268
HOW TO CREATE A QUERY "SELECT DISTINCT designation, state FROM staff"

DESIGNATION AND STATE IS NOT DUPLICATE RECORD PRINT. HOW?

On 03/30/2011 at 4:35:47 AM EDT Kanad wrote:
269
Thanks :). This helped me

On 03/30/2011 at 4:36:11 AM EDT Kanad wrote:
270
Thanks :). This helped me

On 04/11/2011 at 11:23:42 AM EDT Soham wrote:
271
Hi, I have aquery which pulls up the records from two table..... the query is SELECT * FROM DEMF DF, DEMFP DFP WHERE DF.GSNO = DFP.GSNO AND DATE > SYSDATE - 90 AND DF.SNAME = DFP.SNAME AND DF.INAME = DFP.INAME AND DF.SORG = DFP.SORG

The above query gives me duplicate records with the same SNAME,INAME,SORG,GSNO

When i apply distinct for particular colums to be extracted it pulls up the distinct data for the columns named SNAME,INAME,SORG,GSNO but it gives other columns values also which are distinct as compared to the other records hence it results in duplicates.

Can anyone please help me regarding this

On 04/12/2011 at 5:31:06 AM EDT Hitesh wrote:
272
thanks :D this helped me...

On 04/26/2011 at 9:06:33 AM EDT Ed wrote:
273
Hi instead of using SELECT * FROM DEMF DF, DEMFP DFP WHERE DF.GSNO = DFP.GSNO AND DATE > SYSDATE - 90 AND DF.SNAME = DFP.SNAME AND DF.INAME = DFP.INAME AND DF.SORG = DFP.SORG You should use SELECT DISTINT SNAME,INAME,SORG,GSNO FROM DEMF DF, DEMFP DFP WHERE DF.GSNO = DFP.GSNO AND DATE > SYSDATE - 90 AND DF.SNAME = DFP.SNAME AND DF.INAME = DFP.INAME AND DF.SORG = DFP.SORG The *selects all the columns rather than the ones you are interested in.

On 05/02/2011 at 4:09:03 AM EDT deepak wrote:
274
thanks :)

On 05/03/2011 at 4:37:19 AM EDT punit wrote:
275
here is table employee which has the below structure

Emp ID Emp Name Salary 1 Jak 1230 2 Mak 2345 3 Harry 2356 3 Harry 2356 3 Harry 2356 4 John 1345

Write a query to get the list of duplicate records and there count. Also write a query to delete the duplicate record and keep only one record.

On 05/04/2011 at 8:47:25 AM EDT shivakumar wrote:
276
do any one has the code for this: if Ref1, Ref2, Ref3 match & sum of these rows in colums "Amount" turns Zero(0), the referred lines should be deleted from the table (ie in the given example Sl no 100 & 102 should be deleted form the table.

Sl No Ref 1 Ref 2 Ref 3 Amount 100 A 505 700505 10000 101 B 506 700500 5000 102 A 505 700505 -10000 103 B 506 700502 8000

On 05/06/2011 at 12:17:58 AM EDT vinod wrote:
277
how are i retrieve data from database. i have a table in my database but it has no primary key and i want retrieve the 10 row from table after 30 row.

On 05/13/2011 at 7:00:06 AM EDT satish kokate wrote:
278
Thanks :). This helped me

On 05/20/2011 at 11:40:20 AM EDT Anonymous wrote:
279
hello my name is hemraj thakur and i have a problem in sql Query that is.

I've a table Group,marks 1 6 1 10

but i want to result : group,marks 1 6 1 6

in Group 1 the marks 10 not accept. my Email id is:hemgoogali@gmail.com

On 05/20/2011 at 12:38:44 PM EDT HMIles wrote:
280
I want to insert rows that exist in table A but do not exist in table B using multiple fields from table example insert into table b (a,b,c,d,e,f) values (1,2,3,4,5,6) Where a,b,c,d,e,f Not in (select a,b,c,d,e,f from table b)

I cant seem to make it work.. is this possible

On 06/24/2011 at 3:18:35 PM EDT SSR wrote:
281
Regarding : > I want to insert rows that exist in table A but do not exist in table B using multiple fields from table example insert into table b (a,b,c,d,e,f) values (1,2,3,4,5,6) Where a,b,c,d,e,f Not in (select a,b,c,d,e,f from table b) I cant seem to make it work.. is this possible

Hi, You cannot insert the new columns and datas from one table to another table.

Solution: 1.Please use "alter table table b add column(a (datatype),b(datatype)) 2. After adding the needed columns in table B which is available in table a use the following command to insert the values.

Insert into tableb (a,b) as (select a,b from tablea).

On 06/24/2011 at 3:27:23 PM EDT SSR wrote:
282
Regarding : how are i retrieve data from database. i have a table in my database but it has no primary key and i want retrieve the 10 row from table after 30 row.

Hi, You can use rownum or rowid to select the row after 30th row.

select * from tablea where rowid between 30 aand 40

or select * from tablea where rownum between 30 and 40

On 06/27/2011 at 6:50:21 AM EDT kichkich wrote:
283
Hi, I have a table,in that 'Dept' is a column name.when i am trying to get Dept column iam getting repeated(duplicate) values..I want only one value should display,no duplicate values in a single column..plz help me...

On 06/27/2011 at 6:51:17 AM EDT kichkich wrote:
284
Hi, I have a table,in that 'Dept' is a column name.when i am trying to get Dept column iam getting repeated(duplicate) values..I want only one value should display,no duplicate values in a single column..plz help me...

On 07/02/2011 at 5:16:43 AM EDT AIDE EMMANUEL wrote:
285
I have a list of names in a Microsoft access data base table. How can I use SQL Expression to show only names starting with letter R in names field? THX

On 07/12/2011 at 4:51:53 AM EDT Aleem wrote:
286
Hi, how to find unique obseravations? For suppose my data is: eid name values are 101 asdf, 102 xyz, 101 aaa I want output as 101 asdf 102 xyz. Means first occurance is valid and rest are invalid. Can any one suggest me using sql

On 07/14/2011 at 2:25:16 AM EDT neil wrote:
287
this thread is more almost 7 years..

On 07/26/2011 at 2:00:06 AM EDT ninja wrote:
288
thanks! this is an awesome query.

On 07/26/2011 at 5:42:29 AM EDT Sri wrote:
289
Regarding : I have a list of names in a Microsoft access data base table. How can I use SQL Expression to show only names starting with letter R in names field? THX

Hi, Please use the following query to retrive the data for `the name which is starting with R letter

select * form tablename where name like 'R%';

On 08/04/2011 at 12:34:39 AM EDT Guest wrote:
290
you guys sure this works? the definition of Count(column_name)is to count the number of rows in that column, ignoring null values. in other words, you are actually counting the number of users who have email addresses, not the number of times each email address is used.

On 08/24/2011 at 10:37:01 AM EDT Stacey wrote:
291
I want to select all records from a table where field x = a or b. but I only want the records that have field x that = a and field x that = b it would be two separate records. Can anyone help. I tried a union but it is still bringing back records with one or the other and not both.

On 08/26/2011 at 3:16:55 AM EDT Mathi wrote:
292
i need help, delete the duplicates entry from the table and creates new table from duplicates entry.

please some one help me.

thanks in advance - Mathi.

On 08/30/2011 at 3:45:40 AM EDT Arthur Tan wrote:
293
I managed to retrieve the information I needed. But when I want to display information from other columns, the count becomes wrong? What should I do?

Correct (8 results will appear): select t# from table group by t# having count (t#) <= 1;

But when I want to select more columns to display in this query, almost the whole column will show in the result

Wrong (Almost the whole table information will appear): select t#, department, destination from table group by t#, department, destination having count (t#) <= 1;

What should I do?

Thanks.

On 09/08/2011 at 9:40:28 AM EDT Vishal wrote:
294
I have one query as i have two tables viz. tmprospectphone & vi_phone. In Vi_phone table i have 1000 records & in tmprospectphone table i have 500000 records. so i want to compare these two table & display duplicate records which are available. I want to take phone number from vi_phone and want to find duplicate records from tmprospectphone table.

On 09/14/2011 at 7:02:03 PM EDT Jonathan wrote:
295
Wow. What a thread, and how very useful...Can anyone tell me how to get another field from the same table in the output? SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 ) Subquery?

On 09/19/2011 at 3:44:15 PM EDT lee wrote:
296
to delete duplicate rows..

delete from emp where rowid not in (select max(rowid) from emp group by empno, sal);

On 10/14/2011 at 7:47:26 AM EDT goldfather wrote:
297
Distinct is the faster , but using this code help to find the duplicates records by giving >1 in having clause. we can seperate our needs accordingly.. Thanks to poster.. i have learn a bit.. -- Pons

On 11/14/2011 at 3:48:09 AM EST P4uk80 wrote:
298
Hi,

I am having a table in oracle without a primary kay and table is having 5 columns.

Every time while making the insert, i have to ensure that the same combination of records are not bing inserted. For every row if a specific value has been inserted then a comman value like 'ALL' should not be allowed to be inserted.

INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL, ) VALUES (

'Intellect', 'BAU Enhancement', 'DEV', 'Critical')

If the above record is available then the below two record needs to be restricted.

INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL) VALUES (

'Intellect', 'BAU Enhancement', 'DEV', 'Critical')

And

INSERT INTO tbl ( PRODUCT_NAME, REQUEST_TYPE, ENVIRONMENT, PROBLEM_LEVEL) VALUES (

'ALL', 'BAU Enhancement', 'DEV', 'Critical')

Request your help on this.

On 11/19/2011 at 9:12:50 AM EST Anonymous wrote:
299
Hi, Im using oledb

Table 11 columns

when I use the select count it gives me the records and when I type equal i gives me the correct count, however when I used > than it doesn;t work

dim total_winners_cb as new oledb.oledbcommand("Select count(pips) from TRADES where Pips >'0'", con1)

On 01/04/2012 at 3:46:27 AM EST joseph wrote:
300
hey P4uk80, Insert into table name values('&fieldname1','&f2','&f3'); try it

On 01/11/2012 at 6:39:15 AM EST Bharanidharan wrote:
301
select a,b-->contains duplicate from table group by a,b having Count(b)>1

On 03/30/2012 at 7:36:10 AM EDT HANMATH PRADEEP wrote:
302
Hello every1 ……………….. I have a small doubt regarding retrieving records from the base table….. So please clarify my doubt ……………………………..

The respective base table name is “PRADEEP”.

SQL>SELECT *FROM PRADEEP;

SNO NAME ADDR ------------------------------------ 1 HANMATH PRADEEP HYD 2 HARI PRASAD CHENNAI 3 HARI SHANKER BANGLORE 4 HARINATH NAIDU PAKISTAN 5 HARI PRASAD GUNTUR 6 SURESH PAIDY VIZAG

EXPECTING OUTPUT SHOULD BE IN THE FOLLOWING MANNER:

SNO NAME ADDR

1 HANMATH PRADEEP HYD 2 HARI PRASAD CHENNAI 5 HARI PRASAD GUNTUR

MY REQUIREMENT IS AS FOLLOWS:

(1)……… HERE I WOULD LIKE TO RETRIEVE THE RECORDS BASED ON THE NAME COLUMN ONLY.

(2) I KNOW THIS QUERY I.E., (SELECT *FROM PRADEEP WHERE NAME LIKE ‘H_______P%’ OR NAME LIKE ‘H____P%’;) ……………… I DON’T LIKE TO USE SUCH LIKE STATEMENTS HERE? SO PLZ AVOID IT…

(3)……….. THE IMPORTANT CONDITION IS THAT …. THE RECORDS IN WHICH THE STARTING LETTER IS “”H”” (IN MIDDLE NAME) & THE STARTING LETTER IS “”P”” (IN LAST NAME) SHOULD ONLY RETRIEVED FROM THE BASE TABLE.

On 05/01/2012 at 3:25:42 AM EDT sqlhelpneeded wrote:
303
I have a subquery that gets 109 client id's, but 1 is a duplicate. I have a main query that looks in the subquery by client id, but it only returns 108 records. How can I get that last record, even though it's a duplicate?

On 05/03/2012 at 5:11:24 PM EDT chang kit wrote:
304
I need a query which allows user to search for item records by any word that appears in the “notes” field in JFrame

On 05/04/2012 at 1:27:07 AM EDT sqlhelpneeded wrote:
305
here's my query where I need help getting the ages for all the records in the subquery, (there is one duplicate client id in the subquery)

SELECT count(*) & " 14 and under" as [Ages] FROM (SELECT [Client Info].[Client ID] FROM [Client Info] WHERE ((([Client Info].[Start Date]) Between Forms!frmMonthlyStats!txtBegDate And Forms!frmMonthlyStats!txtEndDate and [Client Info].[Age] like "14" or [Client Info].[Age] like "13" or [Client Info].[Age] like "12" or [Client Info].[Age] like "11" or [Client Info].[Age] like "10" or [Client Info].[Age] like "9" or [Client Info].[Age] like "8" or [Client Info].[Age] like "7" or [Client Info].[Age] like "6")) and [Client Info].[Client ID] in (SELECT * from [New Clients Monthly Subquery] ))

I'm thinking I need to enhance the design of the database rather than make the query do all the work?

On 05/04/2012 at 1:49:31 AM EDT Anonymous wrote:
306
I need a query which allows user to search for item records by any word that appears in the “notes” field in JFrame

Anyone has any idea to do this? really need help!

On 05/13/2012 at 5:54:02 PM EDT jfsebastian wrote:
307
PH comment @ 94 - absolute lifesaver if you want to find duplicates of a single field but want to show all the rest of the data for that ID / row Many thanks,

On 05/23/2012 at 1:05:49 PM EDT Anonymous wrote:
308
NEW TO SQL! Need some Help Please.

I am looking to extract data into a new table from a field named Stores if the first 4 letters or characters of each record are equal to one another.

for example if I had a store named abercrombie and another store named abercrombie and fitch.... those would get extracted to a new table.

On 05/24/2012 at 2:34:02 AM EDT Vampslayer017 wrote:
309
we could also use a subquery type thing for the duplicates as well couldn't we ?

select col1 from table1 where col1 NOT IN (select distinct col1 from table1)

On 06/01/2012 at 5:07:02 AM EDT Prakash wrote:
310
Hi, The output of my query contains duplicate records (i.e. column1 value is similar but other column's value different) as well as single record having column2 value blanks(-). now I want to select only one record out of duplicate records for which column2 values are present (eliminating records for which column2 is blanks(-) from duplicate records) and all other single records having column2 values blanks.

My query's Present Output column1 column2 column3 abc xyz pqr abc - klm ijk - uvw plo - ujn yhk ttg wea yhk - erf

Expected Output column1 column2 column3 abc xyz pqr ijk - uvw plo - ujn yhk ttg wea

Can someone help me. Thanks in advance.

On 06/06/2012 at 9:19:51 PM EDT Zebediah wrote:
311
If using the group by, having count functions alone with selecting multiple columns that query will not work. That query should be a subquery that is in the from statement.

On 06/21/2012 at 6:45:46 AM EDT Mohd Shahid Afaque wrote:
312
-- Query to identify duplicate rows Select [Date], Settlement_Period_ID, Profile_Class_ID, GSP_Group, COunt(*) from Table1 Group By [Date], Settlement_Period_ID, Profile_Class_ID, GSP_Group Having COUNT(*) > 1

-- Query to delete duplicate rows delete a from Table1 A -- select Count(*) from Table1 A where A.id > (select min(B.id) from Table1 B where A.GSP_Group = B.GSP_Group and a.Profile_Class_ID = B.Profile_Class_ID and a.Date = b.date and a.Settlement_Period_ID = b.Settlement_Period_ID )

On 06/26/2012 at 4:54:21 AM EDT Anonymous wrote:
313
How do i select a mem_no field which has two equal dates and two equal amounts in a check_no field which contains different values.

On 07/26/2012 at 5:58:03 AM EDT wil wrote:
314
thanks for the solution

On 08/17/2012 at 1:35:36 PM EDT Deadeuzesse wrote:
315
To identify duplicates, I use this set of queries :

-- Identify duplicates on one key -- Gives the list of the keys that have a duplicated record select key_1, count(*) from table_1 group by key_1 having count(*) > 1 ;

-- Same as above but with multiple keys search select key_1, key_2, ..., key_n count(*) from table_1 group by key_1, key_2, ..., key_n having count(*) > 1 ;

-- Match the duplicated keys to their matching record in the table select * from table_1 where key_1 in ( select key_1, count(*) from table_1 group by key_1 having count(*) > 1 );

-- Same as above with multiple key search select * from table_1 where (key_1, key_2, ..., key_n) in ( select key_1, key_2, ..., key_n, count(*) from table_1 group by key_1, key_2, ..., key_n having count(*) > 1 );

-- Associate duplicated record by their unique identifier on a single row -- Requires to have an unique identifier on the table analysed (a primary key for instance) -- Will return cyclic results if the duplicated record count exceeds two results per key select s1.unique_identifier, s2.unique_identifier from ( select unique_identifier, key_1 from table_1 where key_1 in (select key_1 from (select key_1, count(*) from table_1 GROUP BY key_1 having count(*) > 1 )) ) S1, ( select unique_identifier, key_1 from table_1 where key_1 in (select key_1 from (select key_1, count(*) from table_1 GROUP BY key_1 having count(*) > 1 )) ) S2 where S1.key_1 = s2.key_1 and s1.unique_identifier < s2.unique_identifier; -- Same as above with multiple keys search and composed unique identifier select s1.primary_key_field_1, s1.primary_key_field_2, ..., s1.primary_key_field_n, s2.primary_key_field_1, s2.primary_key_field_2, ..., s2.primary_key_field_n from ( select primary_key_field_1, primary_key_field_2, ..., primary_key_field_n, key_1, key_2, ..., key_n from table_1 where (key_1, key_2, ..., key_n) in (select key_1, key_2, ..., key_n from (select key_1, key_2, ..., key_n, count(*) from table_1 GROUP BY key_1, key_2, ..., key_n having count(*) > 1 )) ) S1, ( select primary_key_field_1, primary_key_field_2, ..., primary_key_field_n, key_1, key_2, ..., key_n from table_1 where (key_1, key_2, ..., key_n) in (select key_1, key_2, ..., key_n from (select key_1, key_2, ..., key_n, count(*) from table_1 GROUP BY key_1, key_2, ..., key_n having count(*) > 1 )) ) S2, where S1.key_1 = s2.key_1 and S1.key_2 = s2.key_2 and S1.key_... = s2.key_... and S1.key_n = s2.key_n and S1.primary_key_field_1||S1.primary_key_field_2||S1....||S1.primary_key_field_n < S2.primary_key_field_1||S2.primary_key_field_2||S2....||S2.primary_key_field_n; -- Implicit conversion may sometimes fails according to the primary_key_field types. You may then use conversion fuctions to make them all match the same type Hope it helps !

On 08/17/2012 at 2:42:47 PM EDT sqlnoob wrote:
316
Hey everyone, so I'm there, you have helped me TONS! One more question, here is my sql that works:

select invoice_due_amount, count(invoice_due_amount) As NumOccurrences from finance_invoice where invoice_date>= sysdate - 365 and invoice_record_state = 0 group by invoice_due_amount having (count(invoice_due_amount)>1)

I want it to show me more columns from finance_invoice, but when I add say "invoice_number", it errors out. What do you think?

HELP!!!!!

Thanks, Noob

On 08/17/2012 at 2:44:54 PM EDT Deadeuzesse wrote:
317
Just make sure all selected fields you take are also in the group by section

On 10/18/2012 at 11:05:39 AM EDT JP wrote:
318
Hi everyone. I used the query from the example:

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

But when I put these results in a table using a cgi script, the query returns the last entry from the duplication:

table:

----------------- first duplication ----------------- ----------------- last duplication

query returns the, but I want the first duplication to be returned.

So I thought i had to order the table reversed, so the last entry becomes the first:

last duplication ----------------- ----------------- first duplication -----------------

But when I use ORDER BY, the results are ordered that way. Does someone know how I can fix this problem? Many thanks, JP

On 12/18/2012 at 12:29:04 PM EST Soul wrote:
319
Join Page and Connect IT

https://www.facebook.com/groups/KUSoftwareEngineers/

On 12/18/2012 at 8:39:57 PM EST jd wrote:
320
Responding to Artie's solution for more than one field in finding duplicates in a single table.

I appreciate your solution:

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)

I tried this and it wouldn't work. I found that there has to be a comma after the last field name (e.g. email,) and also the word Occurrences has to be spelled out completely - not Occur. I thought this might help someone else that wasn't that great at SQL yet. Thanks for the thread however!

On 07/03/2014 at 9:25:57 AM EDT HORT wrote:
321
konu hortlam??

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?