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?
p.createdDate
makes all children distinct? Each child has one and only one parent, so only one created date.Child
that have the samefoo
and samebar
. But then, your query doesn't get distinct Child rows, as you claim. It gets distinct foo and bar pairs.