Distinct Children rows ordered by a column of Parent

Question

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?

Answer 1

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.

Answer 2

As @ypercube has pointed out on his comments, You can use GROUP BY and ORDER BY in the same sentence.

create table parent(id int, createdDate date);
insert into parent values (1, '20170301'),(2, '20170201'),(3, '20170101');

create table childs(parent_id int, foo int, var int);
insert into childs values
(1, 1, 1),
(1, 1, 1),
(1, 1, 3),
(2, 2, 10),
(2, 2, 11),
(2, 2, 11),
(3, 3, 20),
(3, 3, 20),
(3, 3, 20);
GO

12 rows affected

select   c.foo, c.var
from     childs c
join     parent p
on       p.id = c.parent_id
group by c.foo, c.var, p.createdDate
order by p.createdDate;

GO

foo | var
--: | --:
  3 |  20
  2 |  10
  2 |  11
  1 |   1
  1 |   3

dbfiddle here