x

How to retrive duplicate record with some condition

Hi,

I have a table like this one.

Sl No   Status
1        M
1        M
2        M
3        N
4        M
4        N
5        M
5        N

I want to fetch those duplicate sl no whose status is M and N.

Means from the above I table i want follwing result

SlNo    Status
4        M
4        N
5        M
5        N

Plz help me.

more ▼

asked May 28 '10 at 03:24 AM in Default

debasish 1 gravatar image

debasish 1
17 6 6 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

This will return a count of the records duplicated.

SELECT  [sl no],  COUNT(*) FROM  dbo.table WHERE  [Status] = 'M' OR [Status] = 'N' GROUP BY  [sl no] HAVING COUNT(*) > 1 
more ▼

answered May 28 '10 at 04:34 AM

sp_lock gravatar image

sp_lock
9k 24 27 30

(comments are locked)
10|1200 characters needed characters left

Try this...

create table mytable (slno int, status char(1)) insert into Mytable select 1,'M' union all select 1,'M' union all select 2,'M' union all select 3,'N' union all select 4,'M' union all select 4,'N' union all select 5,'M' union all select 5,'N'

select * from MyTable

select Mytable.slno, Mytable.status from MyTable join
( select slno from ( select distinct slno, status from MyTable where status in ('M','N') ) distinct_Mytable group by slno having count(*)>1 )dupes on dupes.slno = Mytable.slno
drop table Mytable
more ▼

answered May 28 '10 at 05:18 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 43 49 76

(comments are locked)
10|1200 characters needed characters left

Yes the Query Work........

/* we have to use the Self join between the table... we have to get the duplicate record and and name as Table Alis and then use self join...*/

select Distinct test.id, test.stat from test join
( select id from (select distinct id, stat from test where stat in ('M','N') ) test1 group by id having count(*)>1 )test3 on test3.id = test.id

more ▼

answered May 28 '10 at 06:39 AM

Basit gravatar image

Basit
82 7 7 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x673
x362

asked: May 28 '10 at 03:24 AM

Seen: 1535 times

Last Updated: May 28 '10 at 03:44 AM