SQL: select individual children sorted by child property
I have a query where I want to get individual rows child2
, but ordered by row properties child1
that are associated with a common parent. If I do the following, I get an error because the property is ORDERBY
not in the list DISTINCT
:
select
distinct c2.Id, c2.Foo, c2.Bar
from Child1 c1
join Parent p on c1.parentId = p.Id
join Child2 c2 on c2.parentId = p.Id
order by c1.Id
However, if I add c1.Id
to the picklist, I lose the legibility of the lines Child
as it c1.Id
makes them different.
If I use a CTE or subquery to do the ordering first and then select different rows from that, the outer query does not guarantee that it will maintain the order of the inner / cte query.
Is there a way to achieve this?
source to share
I am doing strings other than cte using TOP(1) WITH TIES
then sorting in query
WITH Data AS(
SELECT TOP(1) WITH TIES
g.id, g.foo, g.bar, p.createdDate
FROM Parent p
JOIN Child c on c.parentId = p.id
JOIN Grandchild g on g.childId = c.id
ORDER BY ROW_NUMBER() OVER(PARTITION BY g.id, g.foo, g.bar ORDER BY p.createdDate)
)
SELECT *
FROM Data
ORDER BY createdDate
TOP(1) WITH TIES
selects rows where ROW_NUMBER()..
= 1. Because of PARTITION BY ...
it selects one row for the section (group). It works similarly GROUP BY
, but allows you to return all columns
source to share