>>I have a SQL which returns some records. The result may look like this:
>>
>>

>>PrimaryKey PrimaryKeyChildTable ChildTableField
>>1                    1                  0
>>4                    5                  1
>>8                    6                  2
>>

>>
>>However, in some particular situations, depending on this big SQL syntax, we might end up with more than one records from the master table such as:
>>
>>

>>PrimaryKey PrimaryKeyChildTable ChildTableField
>>1                    1                  0
>>4                    5                  1
>>4                    6                  2
>>5                    8                  1
>>5                    8                  2
>>8                    6                  2
>>

>>
>>So, on top of the SQL syntax I have presently, I would like to add the necessary code so I would filter any record from a duplicate primary key at the child table level and only keep the record from the child table having ChildTableField=1. So, in such circumstances, when I would have two records from the child table in the query result, I would like to obtain this:
>>
>>

>>PrimaryKey PrimaryKeyChildTable ChildTableField
>>1                    1                  0
>>4                    5                  1
>>5                    8                  1
>>8                    6                  2
>>

>
>

>DECLARE @test TABLE (PrimaryKey int PRIMARY KEY, PrimaryKeyChildTable int,  ChildTableField int)
>INSERT INTO @test
>YourQuery goes here
>
>
>SELECT test.*
>FROM @test test
>INNER JOIN (SELECT PrimaryKey, MIN(PrimaryKeyChildTable) AS PrimaryKeyChildTable
>                   FROM @test
>            GROUP BY PrimaryKey) Tst
>ON Test.PrimaryKey = Tst.PrimaryKey AND
>   Test.PrimaryKeyChildTable = Tst.PrimaryKeyChildTable
>

If you have two identical mimimum records (two same min primarykeychildtable), the above query will return both of them.