please find the table-A structure below:
ID x y z Info

1 1 1 0 dog

1 0 1 0 cat

1 1 1 1 rat

1 0 1 1 mouse

1 0 0 0 none

2 1 1 1 green

2 0 1 0 yellow

2 0 0 0 red

3 0 1 1 sun

3 1 1 0 mon

4 0 1 1 kill

Required logic :

above table needs to be group by using ID column &

Step-1: if x=1, y=1 and z=0 then take info value, else go to step-2

Step-2: if x=0, y=1 and z=0 then take info value, else go to step-3

Step-3: Other than above steps, we need to put null as info value

and required output mentioned in below

ID Info

1 dog

2 yellow

3 mon

4 Null

```
select top(1) with ties
id
,case
when (x=1
and y=1
and z=0
)
or (x=0
and y=1
and z=0
)
then info
else null
end as info
from [table-a]
order by row_number() over(partition by id
order by case
when x=1
and y=1
and z=0
then 0
when x=0
and y=1
and z=0
then 1
else 2
end
)
;
```

1 Like

AndyC
July 29, 2018, 9:22am
#3
```
Select Top (1) With ties
id,
Choose(y&(y^z), info)
From #temp
Order By ROW_NUMBER() Over (Partition By Id Order By y Desc, z Asc,x Desc)
```

1 Like

Assuming that x can be only 0 or 1, then all you really need is:

`case when y=1 and z=0 then info else null end as info`

AndyC
July 30, 2018, 5:55pm
#5
I thought that at first, but the value of info you need depends on the value of x so you need to order the rows carefully to get the correct results.