Hi all,

Im having 12 tables in a database. There are duplications inside the tables,also outside the tables. One record in one table may be duplicated in other table in the database.In this situation which query can i give.Pls help.

Can you post some sample data with expected result?

Select mobileno,min(message) as message, min(date) as date from table

group by mobileno

The above i had given is a sample data. I have 14 colums.

2009489  E     NULL    0  NULL NULL NULL NULL 5/15/2007 2007 6051 NULL NULL NULL
2009489  E     NULL    0  NULL NULL NULL NULL 5/19/2007 2007 6051 NULL NULL NULL
2009489  E     NULL    0  NULL NULL NULL NULL 5/2/2007 2007 6051 NULL NULL NULL
2009489  E     NULL    0  NULL NULL NULL NULL 5/20/2007 2007 6051 NULL NULL NULL
2009489  Hi     NULL    0  NULL NULL NULL NULL 5/3/2007 2007 6051 NULL NULL NULL
2009489  E     NULL    0  NULL NULL NULL NULL 5/4/2007 2007 6051 NULL NULL NULL
2009489  E     NULL    0  NULL NULL NULL NULL 5/6/2007 2007 6051 NULL NULL NULL
2009489  E      NULL    0  NULL NULL NULL NULL 5/7/2007 2007 6051 NULL NULL NULL
2009489  Hello  NULL   0  NULL NULL NULL NULL 5/9/2007 2007 6051 NULL NULL NULL

The above is my original data. Here only date,message is different.wen i give distinct *all these records comes.But i want only one record from this that gives unique mobileno.While selecting i should get in this same format.

Thank U in Advance.

Jeen

You need to extend the query I suggested

Select mobileno,min(col2) as col2, min(col3) as col3, min(col4) as col4..... min(col14) as col14 from table group by mobileno

Thanks a lot...

Just one more doubt.If i have the same mobileno in another table in the same databse.Which query can i give??

Thanks

Jeen

Jeena:

Thanks a lot...

Just one more doubt.If i have the same mobileno in another table in the same databse.Which query can i give??

Thanks

Jeen

You need to join the above query with table2 matching mobileno

Hi.. In the above query if i want to delete the duplicated mobileno from other table,can i give delete instead of select?

Which join should i give?Inner,Outer,Left ....

Thank u.

Jeen

You can also sort and select eliminate duplicates. But that only eliminates dups from your input data. To only add rows that don't already exist, the lookup is the way as you described.
Also blogged about this as well:
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/21/9368.aspx kubj,

Hi..

Im not going to insert a single value into that tables. All that i want to do is to eliminate duplicate records from other tables.A value(in this case mobileno) in one table should not be in the other.

Thanks In advance

Jeen 

I'm having problems trying to understand a requirement where a key value may only occur in one table, and at the same time the key is in use for a number of tables. Could be poor normalization, or the lack of a "status" type of column.

Other than that, sounds like you need some IF EXISTS checks in insert/update triggers.