October 06, 2004
Finding Duplicates with SQL
Here's a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
You could also use this technique to find rows that occur exactly once:
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
Via Huajun Zhai's Blog. Also see How to remove duplicate rows from a table - Microsoft Knowledge base article.
Related Entries
196 people found this page useful, what do you think?
Trackback Address: 169/3EECA31943FD659BFE62CDC156DBABFD
select distinct(email) from users
This works little quicker than your solution.
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.
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.
This was very simple and it works, Thanks
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
Ummm...
DELETE FROM A WHERE A.W <> +
???
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.
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.
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)
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
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
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?
I cannot tell you how long I have spent looking for an answer to this!
Many thanks all
Klyve Dawson
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?
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?
Hi try this!
delete master where email in (select email from anytown);
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.
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 !
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.
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
Great solution for fetching multiple records.
I want to get the column names in a row from a database table where the values are 0
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
Good I like it . Mogambo kush hua !! Ha Ha Mohit
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?
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
I need to find duplicates in a field called ssn and within the duplicates I only want duplicates in different states not same states.
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
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>
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 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?
Thank you it works and saved me a lot of time.
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.
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!
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?
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?
I want to get the column names in a row
http://quadricit.co.nr
Need Help. I am backing up one table to another but I only want to copy the data
The First one is working fine. Thank you
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. >>
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. >>
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.
Struggling New User: Try select distinct count(part_number), part_number from part_number_table group by part_number;
Miller: Thanks a lot, it does the job perfectly! Cheers.
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);
Mark: Try this: SELECT LTRIM(field_name,5), COUNT(LTRIM(field_name,5)) FROM table_name GROUP BY LTRIM(field_name,5);
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;
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;
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;
Miller - Thanks! That worked great! Thanks again
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
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
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
Damn.. such basic questions.... RTFM!
Select names, count(*) from table order by names
an error would display, that names is not a single- group-group function
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.
how to delete the same record from same column and rowid2
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
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?
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
how many times 3 occurs from 3 to 3333
how many times 3 occurs from 3 to 3333?
Not sure how that question relates to sql but that's an easy equation:
restated as how many times x occurs from y to z:
n = (z / x) - (y / x)
If I'm way off topic here i appologize, I'm just browsing through the posts here...
Thanks, I was looking for exactly that. Do I even have to think anymore....?
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!
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)
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))
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
Thank you for this SQL snippet! You really saved me some serious time :)
Thanks a lot mate for posting this simple and sweet solution. Cheers Pete!
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?
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?
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
select * from table1 a where rowid>(select min(rowid) from table2 b where a.col1=b.col1 and a.col2=b.col2...)
select * from table1 a where rowid>(select min(rowid) from table2 b where a.col1=b.col1 and a.col2=b.col2...)
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!
This post helped a lot and worked great. Thanks.
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 )
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
How to find a duplicate records of a table where recordno and course_code in a table
Thanks guys, just what i was looking for.
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
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.
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
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 ----------------
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
The mother of all queries. Ive been looking for something like this since the beginning of my programming career. Kudos!
You can also use 'select distinct' as shown below: SELECT distinct email FROM users
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?
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.
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
how to write output of a sql query to a csv file ?
how to write output of a sql query to a csv file ?
--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
Just a thank you for this. Saved me a few minutes :)
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?
Its really good one... Keep it up.
I have the following column: A, B, C and data for column A and B is duplicated, and need to remove the duplicated records but before I remove the record i have to check column 'B' which has some condtion if column 'B' data is 0 I have to remove all the other data which is duplicated either wise I have o keep the records, which means the first priority is for to get 0.
Example: A B C
1 3 1
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...
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.
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.
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
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)
This is awesome solution.
Hi Mike, just see the comment written by me on 10/12/2007. Hope this will help you.
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?
Thanks guys, Great solution
How to find out duplicate in the same field? Like John John.. Thanks in advance
Simple and 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
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.
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*
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
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.
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
I appreciate your approach at this article .I really liked your tutorial for making a harder issue very simple. All The Best!
I appreciate your approach at this article .I really liked your tutorial for making a harder issue very simple. All The Best!
select count(distinct field1,[field2,...]) from table_one
works a little faster and simpler
$lol = mysql_query("SELECT 'ip' FROM 'brukere' GROUP BY 'ip' HAVING ( COUNT('ip') = 1) "); $kul = mysql_query($lol); ______________________________________
It does'nt work:( Why?
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?
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?
Hello All, This page has really helped me a lot Cheers
Great, simple yet effective code. Helped save me a lot of time. Thanks.
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
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
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 )"
Do you all get partial credit for working out the answers to these kids' homework assignments?
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.
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 ;)
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.
add to del.icio.us
RSS
Pete Freitag is a software engineer, and web developer located in central new york.
