Select rows with a duplicate ID but different value in another column-CodePudding

I have a table like this

I would like to select the Itemid that occurs more than once with a different Rate with group by Masterid

The output should be something like:

CodePudding user response：

You might try the following:

SELECT masterid, detailid, itemid, rate FROM mytable
WHERE (masterid, detailid, rate) IN
(
    SELECT masterid, detailid, rate FROM mytable t
    JOIN mytable o ON o.masterid = t.masterid
        AND o.detailid = t.detailid AND o.rate <> t.rate
    GROUP BY t.masterid, t.detailid, t.rate
    HAVING COUNT(*) >= 2
)

The inner join within the sub-query assures only rows appearing that have an unequal counter part. Alternatively you might add another sub-query condition to the outer query:

AND EXISTS
(
    SELECT * FROM mytable o
    WHERE o.masterid = t.masterid AND o.detailid = t.detailid AND o.rate <> t.rate
)

CodePudding user response：

I believe you are looking for a query like below

select t1.* from t t1 
join 
(
   select masterid,itemid
   from t
   group by masterid,itemid
   having count(distinct rate )>1
)t2 
   on t1.masterid=t2.masterid and t1.itemid=t2.itemid
order by masterid,detailid

and here's a working db fiddle