How to Find Unique Rows without GroupBy, Distinct

I have a SQL field that I only want to appear in a field in my sql query results depending on the value of FamilyType.

So when the Family Type is 3B then I want FamilyID to appear.

I do not want to have duplicate rows (without using distinct or group by or where clause = 3B).

These are my fields in table: Family.

Fields: MemberID, FamilyID, FamilyType.

So there are 2 rows in the order of fields above:

1. 3400, 233, 3B
2. 3400, null, ZH

    select MemberID,  
     CASE WHEN FamilyType = '3B' THEN FamilyID 
     ELSE '' END AS FamilyIDFinal
    from Family

This still produces 2 rows. I would like:

1. 3400, 233

instead of:

1. 3400, 233
2. 3400, null

Any pointers appreciated.

Answer

Well, in theory you can:

select top (1) with ties
  MemberID,
  CASE WHEN FamilyType = '3B' THEN FamilyID ELSE 0 END AS FamilyIDFinal
from Family
order by row_number() over(partition by MemberID order by case FamilyType when '3B' then 1 else 2 end);

However, making this query efficient will be quite difficult. An index like (MemberID, FamilyType) include (FamilyID) should work, but it will be tightly coupled with the logic of this single query. From personal experience, top (1) with ties is usually a solution of the last resort, when everything else fails to deliver. Usually it’s better to revise the data model.