How to get duplicate on PartId and Company Id both and have different

ahmed elbarbary

5y874

How to get duplicate on Part Id and Company Id both and have different Status ?

I work on SQL server 2012 I have issue I can't get duplicate on company id and part id

and on same time have different status

as partid 1211 and companyid 3030 repeated two times meaning two rows but with different status

pending and Done

so How to write query do that

create table #PartsData
(
PartId int,
CompanyId int,
Status nvarchar(50)
)
insert into #PartsData(PartId,CompanyId,Status)
values
(1211,3030,'Pending'),
(1211,3030,'Done'),
(1599,4812,'NotFound'),
(1599,4812,'Pending'),
(9731,4050,'Inprogress'),
(9731,4050,'Done'),
(7801,4031,'Pending'),
(7822,9815,'Pending')
Expected result :
PartId CompanyId Status
1211 3030 Pending
1211 3030 Done
1599 4812 NotFound
1599 4812 Pending
9731 4050 Inprogress
9731 4050 Done

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Accepted answer
If you are inserting duplicate records then you might be in trouble deleting it, you should normalize it.

Anyway putting distinct keyword will fetch you the desired output
SELECT DISTINCT *
FROM #PartsData
WHERE PartId IN
    (     SELECT PartId
          FROM #PartsData
          GROUP BY partid
          HAVING COUNT(*) > 1
    )
ORDER BY PartId
+3
ahmed elbarbary5y ago
thank you for reply
code above will get repeated rows per companyid and partid and this I need
but i need also different status
meaning
I Need below because repeated and different status
PartId    CompanyId    Status
1211    3030    Pending
1211    3030    Done
and I don't need below because repeated and same status
PartId    CompanyId    Status
1599    4812    NotFound
1599    4812    NotFound
+2
Amit Gupta5y ago
Try this
SELECT *
FROM #PartsData
WHERE PartId IN
    (     SELECT PartId
          FROM #PartsData
          GROUP BY partid
          HAVING COUNT(*) > 1
    )
ORDER BY PartId
output:
+2
Jignesh Kumar5y ago
Hi,

If you have only these 3 columns then use distinct,
select distinct * from #PartsData
+2
Ajay Gohil5y ago
Hello,

You can try the following code:

select * from PartsData where PartId=1211;

Note that PartsData doesn't have a primary key.
+2
ahmed elbarbary5y ago
thank you for reply
but i need repeated same partid and company id but different status

and that will give me also status similar

and I need different status
+2
Amit Gupta5y ago
SELECT * FROM PartsData group by PartsId, CompanyId, Status having Count(*)>1
+2