0

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?

3
  • 2
    Please tag your RDBMS
    – McNets
    Jul 5, 2017 at 13:15
  • How can p.createdDate makes all children distinct? Each child has one and only one parent, so only one created date. Jul 5, 2017 at 14:40
  • Unless you have rows in Child that have the same foo and same bar. But then, your query doesn't get distinct Child rows, as you claim. It gets distinct foo and bar pairs. Jul 5, 2017 at 14:48

2 Answers 2

6

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.

4

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

3
  • OK but the problem is (I think) elsewhere. Not they they don't want to see that column, it's that the column alters the results, if it is added in SELECT DISTINCT list. Jul 5, 2017 at 14:50
  • But I retracted my previous comment. I meant: "you don't really need a subquery. You can rewrite SELECT DISTINCT to GROUP BY and still use the CreatedDate in ORDER BY". Jul 5, 2017 at 14:51
  • @ypercubeᵀᴹ ok, I think I got it.
    – McNets
    Jul 5, 2017 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.