SQL: Select distinct children ordered by property of related child

Viewed 924

I've got a query where I want to retrieve distinct child2 rows, but ordered by a property of child1 rows that are related by a common parent. If I do the following, I get an error because the ORDERBY property is not in the DISTINCT list:

select 
    distinct c2.Id, c2.Foo, c2.Bar
    from Child1 c1
    join Parent p on c1.parentId = p.Id 
    join Child2 c2 on c2.parentId = p.Id
    order by c1.Id

However, if I add c1.Id to the select list, I will lose distinctness of Child rows, as c1.Id makes them all distinct.

If I use a CTE or subquery to first do the ordering, and then select distinct rows from that, the outer query doesn't guarantee that it will maintain the order of the inner/cte query.

Is there a way to achieve this?

2 Answers
Related