I have a table like this

enter image description here

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:

enter image description here


2 Answers 2

select tlt.* from table_like_this tlt
  where exists (select masterid, itemid from (
     select distinct masterid, itemid, rate from table_like_this) tlt2
     where tlt2.masterid = tlt.masterid
       and tlt2.itemid   = tlt.itemid
     group by masterid, itemid
     having count(*) > 1);

See https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e09c6647c4c5c04a8013125e83090ec6

PS. images of data are completely useless.

select * 
from (
    select masterid, itemid, detailid, dense_rank()over( order by itemid desc)rk 
    from testorder
    group by masterid, itemid, detailid
where rk=3
order by masterid asc
  • 3
    It would be helpful to explain how this answers the question.
    – Hannah Vernon
    Commented Sep 1, 2022 at 12:27
  • is it wrong approach??
    – megha
    Commented Sep 3, 2022 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.