one-to-many query selecting all parents and single top child for each parent

greatest-n-per-group, sql, sql-server, t-sql

Solution

select p.id, p.text, c.id, c.parent, c.feature
from Parents p
left join (select c1.id, c1.parent, c1.feature
             from Childs c1
             join (select p1.id, max(c2.feature) maxFeature
                     from Parents p1
                left join Childs c2 on p1.id = c2.parent
            group by p1.id) cf on c1.parent = cf.id 
                              and c1.feature = cf.maxFeature) c
on p.id = c.parent

Problem

There are two SQL tables: ``` Parents: +--+---------+ |id| text | +--+---------+ | 1| Blah | | 2| Blah2 | | 3| Blah3 | +--+---------+ Childs +--+------+-------+ |id|parent|feature| +--+------+-------+ | 1| 1 | 123 | | 2| 1 | 35 | | 3| 2 | 15 | +--+------+-------+ ``` I want to select with single query every row from Parents table and for each one single row from Childs table with relation "parent"-"id" value and the greatest "feature" column value. In this example result should be: ``` +----+------+----+--------+---------+ |p.id|p.text|c.id|c.parent|c.feature| +----+------+----+--------+---------+ | 1 | Blah | 1 | 1 | 123 | | 2 | Blah2| 3 | 2 | 15 | | 3 | Blah3|null| null | null | +----+------+----+--------+---------+ ``` Where p = Parent table and c = Child table I tried to LEFT OUTER JOIN and GROUP BY but MSSQL Express told me that query with GROUP BY require Aggregate functions on every non-Groupped fields. And I do not want to Group them all, but rather select top row (with custom ordering). I am totally out of ideas...

Original source