This works little quicker than your solution.

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.

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

DELETE FROM A WHERE A.W <> +

???

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.

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)

How would I actually delete all the duplicate entries that had the duplicates based on more than one field?

Many thanks all

Klyve Dawson

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

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

Any suggestions would be greatly appreciated.

Have you found a solution for this ? Thanks for sharing !

can some one help me on that.

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

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

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 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

set rowcount <N-1> delete from tablename where <condition>

I hope this helps someone else!

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?

element name Table name m A m B

thanks for ur help

Tx for the help in advance.

Does this make sense? Any ideas to to accomplish this? Thanks folks!

can anyone help?

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?

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. >>

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. >>

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.

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

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

In fact, I went further and found not only the dupes, but just *how* duplicated they are. This's my query:

SELECT field3, COUNT (field3) AS count FROM table2 GROUP BY field3 HAVING ( COUNT (field3) > 1) ORDER BY count DESC, field3

The results start with the entries duplicated the *most*, and continue on to the ones duplicated only twice.

Anyway, yer post helped me out a lot, and next time I'm in New York, I ought to buy you a drink.

I 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

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!

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

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.

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))

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'

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?

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

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!

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 )

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.

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

---------------- 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 ----------------

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

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

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

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?

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

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

That should do the trick...

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 want to select users who are belonging to more than one group.

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

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)

please advice

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

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*

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.

Thank you. Serj

works a little faster and simpler

It does'nt work:( Why?

How would i do that?

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?

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

This, based on the query above, does work:

SELECT COUNT(email), name AS NumOccurrences FROM user GROUP BY email HAVING ( COUNT(email) > 1 )"

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

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

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 name, email COUNT(name) AS NumOccurName, COUNT(email) as NumOccurEmail FROM users GROUP BY Name, Email HAVING ( COUNT(name) > 1 ) AND ( COUNT(email) > 1)

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)

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

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

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

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

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 );"

"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"

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

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

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

// 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

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.

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

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

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.

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.

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

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!

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

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

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

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

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 |

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

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'

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'

Right i see. try

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

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

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

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.

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 ?

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.

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

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

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

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. ;)

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

Where do I go to do all of this?

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!

Thanks in Advance Jagdeep Mankotia

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

Any Soln will be highly appreciable.

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

Any Soln will be highly appreciable.

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

Any Soln will be highly appreciable.

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

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 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

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

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

===== 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

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 )

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.

example

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

but I get errors

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!

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

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

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

DESIGNATION AND STATE IS NOT DUPLICATE RECORD PRINT. HOW?

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

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.

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

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 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).

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, 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%';

please some one help me.

thanks in advance - Mathi.

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.

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

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.

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)

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.

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?

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

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.

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

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

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

Can someone help me. Thanks in advance.

-- Query to 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 )

-- 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 !

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

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

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

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!