Complex query - derive distinct row - based on certain condition

[aris21]

i have sample rows in excel sheet , i need to select distinct rows based on condition

if we see pink color rows , they have same every column but different event id-- so in this case i need to keep only the first row and discard the next row( as its duplicate )

if we see green color rows , Event IDs are same but Cl ID is different . I need to keep all rows

attached input / output sheet .
can someone help me on writing a query for this

output should contain row number 2,4,5,6,7,8

[NoellaG]

if the data are retrieved from SQL server, a simple distinct should do the trick :

select distinct [Date],[CI ID], [Emp ID], [Emp Name], ... ,[Product ID] from [MyTableName]

[aris21]

but this query above as u mentioned just gives the same set of rows
is there any other way

[harishgg1]

hi

i have a doubt

ALL CI ID's are different keep all the rows
OR
ANY CI ID's are different keep all the rows

CI ID
1
2
1
2
4
5

OR

CI ID
1
2
3
4
5

[harishgg1]

hi

hope this helps

i took into account ALL CIID 's will be different

create data script

drop table if exists #Temp

create table #Temp ( Date1 date, CIID int , EmpID int , EmpName varchar(10), EmpRole varchar(10) , EventID int , ProductID int )
insert into #Temp select '2022-12-31', 7810 , 8485 , 'sam', 'teacher' , 5662 , 35
insert into #Temp select '2022-12-31', 7810 , 8485 , 'sam', 'teacher' , 5690 , 35
insert into #Temp select '2022-10-31', 890 , 2100 , 'joe', 'doctor' , 2241 , 80
insert into #Temp select '2022-10-31', 521 , 2100 , 'joe', 'doctor' , 2241 , 80
insert into #Temp select '2022-10-31', 660 , 2100 , 'joe', 'doctor' , 2241 , 80
insert into #Temp select '2022-10-31', 255 , 2100 , 'joe', 'doctor' , 2241 , 80
insert into #Temp select '2022-10-31', 359 , 2100 , 'joe', 'doctor' , 2241 , 80

; with cteEventID   as (select rank() OVER(partition by empid order by EventID) as rn, * from #Temp)
     , cteCIID      as (select empid , CASE WHEN count(distinct CIID)= count(CIID) THEN 1 ELSE 0 END as CIID_distinct from  cteEventID where rn =1  group by  empid ) 
     select 
	    b.* 
 	 from 
	    cteCIID a 
		   join 
	   cteEventID b 
	       on a.EmpID = b.EmpID 
      where 
	      b.rn =1 and a.CIID_distinct = 1