You may want to check our blogs on this topic.

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


And to answer your question with a quickest sample it would be

select PrimaryKey, PrimaryKeyChildTable, ChildTableField
 from (select PrimaryKey, PrimaryKeyChildTable, ChildTableField, 
row_number() over (partition by PrimaryKey 
ORDER BY PrimaryKeyChildTableField) as RowNum from myTable) X where RowNum = 1

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