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, 2010 at 03:24 AM in Default

avatar image

debasish 1
17 6 7 9

(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, 2010 at 04:34 AM

avatar image

sp_lock
10.5k 27 37 37

(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, 2010 at 05:18 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

(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, 2010 at 06:39 AM

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

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:

x992
x426

asked: May 28, 2010 at 03:24 AM

Seen: 2377 times

Last Updated: May 28, 2010 at 03:44 AM

Copyright 2016 Redgate Software. Privacy Policy