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