I've got a query where I want to retrieve distinct
Child rows, but ordered by a column of
Parent. If I do the following, I get an error because the column specified in the
ORDER BY is not included in the
SELECT DISTINCT c.foo, c.bar FROM Parent p JOIN Child c on c.parentId = p.id ORDER BY p.createdDate
However, if I add
p.createdDate to the select list, I will lose distinctness of
Child rows, as
p.createdDate 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?