Hi All,
I have a list of records with "CORP_ID" and "INITIAL" columns
I am trying to delete records with the following condition;
Group the records based on "CORP_ID" and "INITIAL" columns and delete the records where the initial "JM" is not part of this group.
In this example, 3rd row (512 - GR) should be deleted.

CORP_ID INITIAL
346 JM
346 LB
512 GR
742 JM
742 LB
742 LH

Thanks for your help,

:)


MCinar

Love all the creatures because of the creator.

__________________
MCinar

Love all the creatures because of the creator.

May be there is an easier way but here is my query

DELETE
FROM MyTable AS T1
WHERE T1.CORP_ID NOT IN (SELECT T3.Corp_ID FROM (SELECT T2.CORP_ID,T2.INITIAL
FROM MyTable T2
GROUP BY CORP_ID, INITIAL
HAVING INITIAL = 'JM') As T3);

Hi Chirag,
I tried and it worked very well.

Thanks for your help,



MCinar

Love all the creatures because of the creator.