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?

http://quadricit.co.nr

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

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

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

Not sure how that question relates to sql but that's an easy equation:

restated as how many times x occurs from y to z:

n = (z / x) - (y / x)

If I'm way off topic here i appologize, I'm just browsing through the posts here...

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 1

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

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