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?