>>>>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.