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.



Related Entries

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

Trackbacks

Comments

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.
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?
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!
Thanks a lot!
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?
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;
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
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
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 ?
--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 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
thank you so much...
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.
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?
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
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
Good Solution. This helped me a lot.
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
"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.
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.
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)
Thanks for your solution , it works fine.
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)
how to find out the duplicate values on the table for more than one column
Thank you for giving the answer
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
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
I used Emmanuel's solution, the fourth comment, and it works beautifully for many columns. Include all columns in your table and voila!
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
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 );"
Great Solution, Thanks
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 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?
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
Thanks Mate !!!!
Thanks Mate !!!!
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
Thanks for this post, is simple and works great!
so no one came up with a way to select *only* the duplicate rows?
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
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.
"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.
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 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.
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?
Look at this desision
"http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/"
Great Work!

Works like a charm, and can be used in sub-queries
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)
cheers hopefully will help
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..
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..
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/
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
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.
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?
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??
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 |
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
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..
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'
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'
@ Francois

Right i see. try

Select * from table where Status = 'n' AND id = 2
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
thanks for the solution
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 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 ?
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
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?
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
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
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
select mobileno
from table1
group by mobileno
having count(*)>1
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!
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
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
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.
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.
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.
Simple and effective, works like a charm!
Hi

I would like to know the DATABASE concepts. Could anyone can provide good URS's for DATABASE concepts, it would be grateful.
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.
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

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
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
Its simple Delete your table data and starts a new one! :D
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.
thanks. it helped me..
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?
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! I used this query to show a client how terrible it is to pull records by name instead of ID. He got it. :D
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 :)
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
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...
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.
thanks :)
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.
Thanks :). This helped me
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
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
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).
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
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...
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...
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, 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
this thread is more almost 7 years..
thanks! this is an awesome query.
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%';
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.
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 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.
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?
to delete duplicate rows..

delete from emp where rowid not in (select max(rowid) from emp group by empno, sal);
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
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.
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)
hey P4uk80,
Insert into table name values('&fieldname1','&f2','&f3');
try it
select a,b-->contains duplicate
from table
group by a,b
having Count(b)>1
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.
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?
I need a query which allows user to search for item records by any word that appears in the “notes” field in JFrame
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?
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!
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,
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.
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)
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.
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.
-- 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
)
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.
thanks for the solution
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
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
Join Page and Connect IT

https://www.facebook.com/groups/KUSoftwareEngineers/
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!
konu hortlam??

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?