Finding Duplicates with SQL

October 06, 2004
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.


Like this? Follow me ↯


You might also like:

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

Comments

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.
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.
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
How can i drop more than one table in a single command ( table names having some characters common)??
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
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
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?
How can i find out same row in sql table? Pls give me the query for tht. Thanks.
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.
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.
Great solution for fetching multiple records.
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)?
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
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
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
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.
I want to select distinct record from any table . Which is the best method. (According to Performance)
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?
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!
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
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!
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
How to find out the SQL query that was used to create a view in DB2 Tx for the help in advance.
Damn.. such basic questions.... RTFM! Select names, count(*) from table order by names
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.
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!
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.
Select * from table1 Where KEY_ID IN (Select MIN(KEY_ID) FROM table1 Group by REPEATED FIELDS Having count(REPEATED FILEDS) > 1
How to find out column name of the table by passing only field number to the procedure.
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.
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
Worked for me except I needed to check a Unigueidentifier field and SQL will not allow a count on that data type.
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.
Great Solution. I was looking for this, just typed it in google and saw your page. Thanks.
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!
count how many records in two tables and return sum of both tables
how can this work if i want to group the emails after the subject and to return their email id?
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
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'
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?
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
How to find a duplicate records of a table where recordno and course_code in a table
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
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
You can also use 'select distinct' as shown below: SELECT distinct email FROM users
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.
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
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?
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
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
delete from table That should do the trick...
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
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?
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.
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)
Thanks guys, Great solution
how can write a query for that displays all the names which contains first and last alplhabets are same
sir, i want latest (nov,dec) OCA Dumps please advice
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
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
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
thank you so much...
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?
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
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
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)
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)
Thank you for giving the answer
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
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
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
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
Great Solution, Thanks
@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"
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
Dear friends,, I m new in sql how to create a table..
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/
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
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
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.
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
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
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?
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.
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?
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?
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!
SELECT *
FROM maintable p
WHERE ((SELECT COUNT(id)
FROM maintable
WHERE p.columnwithdupes = maintable.columnwithdupes AND p.id <> maintable.id) > 0)
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
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/
You can use.
SELECT * FROM [your_table_name] ORDER BY [your_table_name].[date] ASC
customer table have some duplicate records i want display duplicate records?can u give solutios anybody.
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.
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..
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
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
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.
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
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.
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
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;
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;
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. ;)
I want to display for me the following output 1/11+11/22+22/33+33/44+44/55 by using for loop
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?
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

===========

Where do I go to do all of this?
Ok I got.
You go to the query form, copy and paste what you've written
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
Hi

I would like to know the DATABASE concepts. Could anyone can provide good URS's for DATABASE concepts, it would be grateful.
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
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
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
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 )
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
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.
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
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.
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.
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
assume that one column has 10 values...sum has duplicates...how to find which value has maximum number of duplicates in sql
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..
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!
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
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.
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;
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
HOW TO CREATE A QUERY
"SELECT DISTINCT designation, state
FROM staff"

DESIGNATION AND STATE IS NOT DUPLICATE RECORD PRINT. HOW?
Thanks :). This helped me
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
thanks :D this helped me...
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.
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
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.
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
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
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.
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.
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.
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?
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.
I need a query which allows user to search for item records by any word that appears in the “notes” field in JFrame
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.
-- 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
)
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 !
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
Just make sure all selected fields you take are also in the group by section


Foundeo Inc.