Sql Server-Duplicate records

[jeenajos]

Hi all,

Im having problem and im spending half of the time for this. I have a databse for sms chat service. and a table containing the details such as mobileno,message,language,place,category,date,year .In this table i have a huge number of records. Now the problem is that i need to sort my table. I need the result in the same format(mobileno,message,language,...).And no duplication for mobileno.

Same mobileno will send more than one message.But i need only unique mobileno.In this situtaion what query can i give. I tried with some select query but all are giving the duplication of mobileno.

Pls help if anyone knows abt this.

Thanks in advance.

Jeen

[azimmer]

Hi all,

Im having problem and im spending half of the time for this. I have a databse for sms chat service. and a table containing the details such as mobileno,message,language,place,category,date,year .In this table i have a huge number of records. Now the problem is that i need to sort my table. I need the result in the same format(mobileno,message,language,...).And no duplication for mobileno.

Same mobileno will send more than one message.But i need only unique mobileno.In this situtaion what query can i give. I tried with some select query but all are giving the duplication of mobileno.

Pls help if anyone knows abt this.

Thanks in advance.

Jeen

Do you want the unique mobile numbers only (i.e. ones that show up exactly once in your table), or do you want to do something with the duplicated ones (e.g. show the first or last record)?

One solution to the first goes along the following pattern:

select y.mobileno,y.message,y.language,y.place,y.category,y.date,y.year
from yourTable as y INNER JOIN
 (select mobileno, count(mobileno)
  from yourTable
  having count(mobileno)=1) as x ON y.mobileno=x.mobileno
order by y.mobileno,y.message,y.language,y.place,y.category,y.date,y.year
 

[jeenajos]

Hi,
The duplication is for my mobileno.Some times date and year too bw duplicated.
In the same day itself one can send has many sms as they want. But i want a record in which any one of there message is present.
I need it in the above format itself.Not the mobileno or message alone.

I wil give an eg:
No Mess Date Year
1234 Hi 10Aug 2007
5487 Hi 22Aug 2007
1234 He 22Aug 2006

In this case the result shuld be like

1234 ---- ----- -----
5487 Hi 22Aug 2007

Any one record of No 1234.

Hope u can help me.
Thanks in advance

Regards
Jeen

[azimmer]

Hi,
The duplication is for my mobileno.Some times date and year too bw duplicated.
In the same day itself one can send has many sms as they want. But i want a record in which any one of there message is present.
I need it in the above format itself.Not the mobileno or message alone.

I wil give an eg:
No Mess Date Year
1234 Hi 10Aug 2007
5487 Hi 22Aug 2007
1234 He 22Aug 2006

In this case the result shuld be like

1234 ---- ----- -----
5487 Hi 22Aug 2007

Any one record of No 1234.

Hope u can help me.
Thanks in advance

Regards
Jeen

The code in my previous post should be almost OK except that it omits all duplicate mobileno-s. The one that meets your format in the post (i.e. the one with dashes) is as follows:

select *
from
(
select y.mobileno,y.message,y.language,y.place,y.category,y.date,y.year
from yourTable as y INNER JOIN
 (select mobileno, count(mobileno)
  from yourTable
  having count(mobileno)=1) as x ON y.mobileno=x.mobileno
union
select z.mobileno, '-----' as z.message,'-----' as z.language,'-----' as z.place,'-----' as z.category,'-----' as z.date,'-----' as z.year
from yourTable as z INNER JOIN
 (select mobileno, count(mobileno)
  from yourTable
  having count(mobileno)>1) as xx ON z.mobileno=xx.mobileno
)
order by mobileno
 

Hope it helps.

[jeenajos]

Hi..
Thank u for the solution.
But in this situation im having about 2 crores of records.I cant specifically write the message or date.In ur previous query u had used union and al.But the ---- means i want to sort the message according to the messages or date or year know..Its huge record that i cant give one by one

Hope u get me.
Thank U.
Regards
Jeen