Distinct Children rows ordered by a column of Parent 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 DISTINCT list:

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?
You need to GROUP BY instead of DISTINCT (the effect is the same) and you need to aggregate the column you want to use for sort order. In this case I used MIN, but you can use whatever makes sense here.

SELECT c.foo, c.bar
FROM Parent p
JOIN Child c on c.parentId = p.id
GROUP BY c.foo, c.bar
ORDER BY MIN(p.createdDate);


Please note that, since you're ordering by a column of the parent table, you may have multiple rows from the child table that have the same value for Parent.createdDate, so the sort order within the same createdDate will be non predictable. You need to add at least one column from the child table to make the sort order predictable.