Pete Freitag Pete Freitag

Finding Duplicates with SQL

Updated on May 05, 2022
By Pete Freitag

Let's suppose you coded a email newsletter signup form, but you forgot to double check that the email address was not a duplicate, or already in the database. We can write a query to find all the emails in our table that are duplicates, or occurs in more than one row.

The following SQL query works great for finding duplicate values in a table.

SQL To Find Duplicates

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

By using group by and then having a count greater than one, we find rows with with duplicate email addresses using the above SQL.

Find Rows that Occur Once

The opposite of our duplicates SQL query, we can use a variant of that SQL statement to find rows that occur exactly once:

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

Original Sources: Huajun Zhai's Blog, How to remove duplicate rows from a table - Microsoft Knowledge base article (139444).

sql databases duplicates

Finding Duplicates with SQL was first published on October 06, 2004.

If you like reading about sql, databases, or duplicates then you might also like:

Discuss / Follow me on Twitter ↯


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.
by Emanuel on 11/19/2004 at 6:25:20 PM UTC
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
by murali on 08/23/2005 at 5:37:07 PM UTC
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
by Mooky Desai on 10/24/2005 at 5:42:01 PM UTC
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?
by Frustrated on 10/27/2005 at 1:26:31 PM UTC
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.
by Sangeetha K on 11/24/2005 at 11:42:13 AM UTC
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.
by sam on 11/25/2005 at 5:32:47 AM UTC
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)?
by PH on 12/13/2005 at 11:16:29 PM UTC
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
by AJ on 12/16/2005 at 10:11:45 AM UTC
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?

by Mark on 12/18/2005 at 1:51:30 AM UTC
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


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

I think this will work
by Nailesh on 12/27/2005 at 6:37:18 AM UTC
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.
by amir on 01/06/2006 at 7:16:42 PM UTC
I want to select distinct record from any table .
Which is the best method. (According to Performance)
by Arvind Singh on 01/15/2006 at 1:29:16 AM UTC
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?
by Brian Moore on 01/16/2006 at 1:04:17 PM UTC
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!
by Anonymous on 02/02/2006 at 1:19:36 PM UTC

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

by david on 02/02/2006 at 1:28:25 PM UTC
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!
by Anonymous on 02/03/2006 at 9:26:37 AM UTC
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
by lalith on 02/09/2006 at 9:31:21 AM UTC
How to find out the SQL query that was used to create a view in DB2

Tx for the help in advance.
by chat on 02/17/2006 at 1:46:50 PM UTC
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.
by Brandon on 03/21/2006 at 9:37:32 AM UTC
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.
by Milelr on 04/07/2006 at 1:56:53 PM UTC
Select * from table1
Where KEY_ID IN (Select MIN(KEY_ID)
Having count(REPEATED FILEDS) > 1
by Pvedi on 04/12/2006 at 1:33:45 PM UTC
How to find out column name of the table by passing only field number to the procedure.
by prashant on 04/17/2006 at 5:16:05 AM UTC
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.
by Miller on 04/17/2006 at 7:11:17 AM UTC
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.

by pdelaurentis on 04/18/2006 at 11:27:18 PM UTC
Worked for me except I needed to check a Unigueidentifier field and SQL will not allow a count on that data type.
by Walt on 05/11/2006 at 9:50:28 AM UTC

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.
by Vasily on 05/15/2006 at 10:28:16 PM UTC
Great Solution. I was looking for this, just typed it in google and saw your page. Thanks.
by jj on 05/23/2006 at 9:56:06 AM UTC
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!
by Sujit on 06/08/2006 at 6:56:18 PM UTC
count how many records in two tables and return sum of both tables
by Ranjeet Kumar Bhatia on 06/23/2006 at 4:51:29 AM UTC
how can this work if i want to group the emails after the subject and to return their email id?
by bogdan on 07/10/2006 at 8:23:28 AM UTC
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
by Vijay on 08/18/2006 at 6:49:14 AM UTC
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?
by Jasmine on 10/12/2006 at 7:16:07 AM UTC
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, ...

This will return rows having duplicates.

Stan Daymond, London, UK
by Stan Daymond on 10/25/2006 at 4:45:33 AM UTC
How to find a duplicate records of a table where recordno and course_code in a table
by arun on 01/09/2007 at 10:31:42 PM UTC
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
by sikruti on 02/27/2007 at 2:18:32 AM UTC
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>1
by Avijt Pramanik on 03/19/2007 at 11:13:30 PM UTC
You can also use 'select distinct' as shown below:
SELECT distinct email
FROM users
by Adam on 04/10/2007 at 8:10:20 PM UTC
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.
by Rajini on 05/06/2007 at 9:55:46 PM UTC

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?

by Srikanth on 05/14/2007 at 5:04:33 PM UTC
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?
by Eric on 08/09/2007 at 2:31:57 PM UTC
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.


1 3 0
1 3 2

I need to have the result of

1 3 0

Please any idea?

by Daniel on 08/22/2007 at 2:01:04 PM UTC
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?


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
by Shrey on 08/23/2007 at 8:49:28 AM UTC
delete from table

That should do the trick...
by Ana on 08/23/2007 at 5:50:02 PM UTC
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
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.

Sinoy Xavier
Infosys, Bangalore
by Sinoy Xavier on 10/12/2007 at 12:07:00 AM UTC
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?
by Bobby on 11/08/2007 at 4:42:04 PM UTC
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.
by Lakshmi on 11/14/2007 at 6:42:27 AM UTC

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)
by Mike on 11/26/2007 at 7:23:02 AM UTC
Thanks guys,
Great solution
by suraj on 12/04/2007 at 10:43:55 PM UTC

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
by Akila on 01/08/2008 at 10:31:58 AM UTC
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
by ron on 01/18/2008 at 12:20:42 AM UTC
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.
by serj on 01/30/2008 at 4:44:44 PM UTC
thank you so much...
by Kannan P on 02/06/2008 at 12:13:16 AM UTC
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)
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
by Gajendra on 10/30/2008 at 12:28:08 PM UTC
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
by KN on 01/28/2009 at 7:17:27 PM UTC
@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"
by Terry Pearson on 03/06/2009 at 12:51:36 PM UTC
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?
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
by Suriya on 04/13/2009 at 12:03:56 AM UTC
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..

by Vignesh on 09/16/2009 at 5:50:26 AM UTC
Vignesh, try this:
create table table c as
select last name, first name, country, new table from table a
select last name, first name, country,old table from table b;

This will give you all the unique records in a and b
by Anon on 10/01/2009 at 2:35:05 PM UTC
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.
by Anon on 10/14/2009 at 5:59:16 PM UTC
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
by Lui on 11/18/2009 at 12:12:07 AM UTC
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.
by je on 12/04/2009 at 5:01:44 PM UTC
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.


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!
by Richard on 01/25/2010 at 11:21:35 AM UTC
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.
by hanuman on 02/10/2010 at 7:03:26 PM UTC
You can use.
SELECT * FROM [your_table_name] ORDER BY [your_table_name].[date] ASC
by Ansari Mohammad Qayamuddin. H on 02/22/2010 at 6:38:39 AM UTC
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.
by kiran on 02/27/2010 at 12:57:26 PM UTC
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
by Francois on 04/15/2010 at 3:38:20 AM UTC
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.
by Trev on 05/04/2010 at 5:19:16 PM UTC
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
by Trev on 05/04/2010 at 5:21:28 PM UTC
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.
by jyothi on 06/06/2010 at 11:45:15 AM UTC
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. ;)
by markw on 06/24/2010 at 7:29:31 AM UTC
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?
by domenic on 07/07/2010 at 10:15:31 AM UTC
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )


Where do I go to do all of this?
by aa33030 on 08/21/2010 at 7:37:35 PM UTC

I would like to know the DATABASE concepts. Could anyone can provide good URS's for DATABASE concepts, it would be grateful.
by Daruru on 10/07/2010 at 3:19:53 AM UTC
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"
((select A,B,C from TEMP_TABLE
group by A,B,C
having count(*) > 1) <5

by Santu on 11/08/2010 at 3:21:04 AM UTC
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 = p.validcode && p.productcategory = 'Others'). please any body that can assist
by ooliki on 01/10/2011 at 8:16:55 AM UTC
assume that one column has 10 values...sum has to find which value has maximum number of duplicates in sql
by ela on 01/20/2011 at 1:05:15 AM UTC
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..
by hazari on 02/03/2011 at 5:56:44 AM UTC
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:


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!
by Marcus on 02/03/2011 at 6:57:38 AM UTC
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.
by Mohsin on 03/01/2011 at 12:02:06 PM UTC
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.
by vinod on 05/05/2011 at 10:17:58 PM UTC
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
by HMIles on 05/20/2011 at 10:38:44 AM UTC