>>>>>Hi
>>>>>
>>>>>I have a list of individuals with a key of individual_ref
>>>>>those individuals can be linked to a partner by family_ref
>>>>>
>>>>>so I would have a row
>>>>>
>>>>>individual_ref family ref
>>>>>58056 174279
>>>>>174279 58056
>>>>>
>>>>>I am selecting a random number of individual but I don't want partners to be selected
>>>>>
>>>>>How can I do that.
>>>>>
>>>>>Thanks
>>>>>
>>>>>Nick
>>>>
>>>>
>>>>UPDATE:
>>>>I just saw that your data has cross reference. How do you know what is partner and what is family?
>>>>From the data you provided which record you want?
>>>
>>>It doesn't matter
>>>
>>>I am randomly selecting and I don't want the random selection to pull out someone and their partner.
>>>
>>>hanks
>>
>>
>>Something like:
>>

>>IF NOT EXISTS(SELECT * FROM sys.tables WHERE NAME = 'Test')
>>BEGIN
>>CREATE TABLE Test (PartnerId int, FamilyId int)
>>
>>INSERT INTO test VALUES(1,2)
>>INSERT INTO test VALUES(2,1)
>>INSERT INTO test VALUES(3,4)
>>INSERT INTO test VALUES(4,3)
>>INSERT INTO test VALUES(5,6)
>>INSERT INTO test VALUES(6,5)
>>END
>>GO
>>
>>
>>;WITH cteTest(PartnerId, FamilyId)
>>AS
>>( SELECT TOP 4 * FROM Test ORDER BY NEWID())
>>
>>SELECT cteTest.* FROM cteTest
>>LEFT JOIN cteTest ctt ON cteTest.PartnerId = ctt.FamilyId
>>WHERE ctt.FamilyId IS NULL
>>

>>?
>>
>>There is no guarantee that you always will have 4 records though.
>
>I believe you need to reverse the condition, e.g.
>

>;with cte as (select T.*, row_number() over (order by NewID()) as RandomRow 
>from Test T where not exists (select 1 from Test T2 where T.FamilyID = T2.PartnerID))
>
>select * from cte where RandomRow <=4

>
>The above is from the top of my head - and may be a bit slow.


With such cross data (if you see Nicholas has the same IDs in BOTH fields) you wouldn't get any rows.