one-to-many query selecting all parents and single top child for each parent
Solution 1
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
Solution 2
Using CTE (SQL Server 2005+):
WITH max_feature AS (
SELECT c.id,
c.parent,
MAX(c.feature) 'feature'
FROM CHILD c
GROUP BY c.id, c.parent)
SELECT p.id,
p.text,
mf.id,
mf.parent,
mf.feature
FROM PARENT p
LEFT JOIN max_feature mf ON mf.parent = p.id
Non CTE equivalent:
SELECT p.id,
p.text,
mf.id,
mf.parent,
mf.feature
FROM PARENT p
LEFT JOIN (SELECT c.id,
c.parent,
MAX(c.feature) 'feature'
FROM CHILD c
GROUP BY c.id, c.parent) mf ON mf.parent = p.id
Your question lacks details for handling tie breakers (when 2+ CHILD.id
values have the same feature value). Agent_9191's answer uses TOP 1
, but that will take the first that is returned & not necessarily the one you want.
Solution 3
This should work:
SELECT p.id, p.text, c.id, c.parent,c.feature
FROM parent p
LEFT OUTER JOIN (SELECT TOP 1 child.id,
child.parent,
MAX(child.feature)
FROM child
WHERE child.parent = p.id
GROUP BY child.id, child.parent
) c ON p.id = c.parent
Solution 4
manji's query does not handle tie-breakers for max feature. Here is my method, which I've tested:
;WITH WithClause AS (SELECT p.id, p.text,
(SELECT TOP 1 c.id from childs c
where c.parent = p.id order by c.feature desc)
AS BestChildID
FROM Parents p)
SELECT WithClause.id, WithClause.text, c.id, c.parent, c.feature
FROM WithClause
LEFT JOIN childs c on WithClause.BestChildID = c.id
Solution 5
If you need to join different from a MAX column and any columns described in a group by closure of a nested select, you can use an APPLY function. It is a simplest solution. You also can use WITH operator. But that look harder.
SELECT p.id, p.text, CHILD_ROW.ANY_COLLUMN
FROM parent p
OUTER APPLY (SELECT TOP 1 child.ANY_COLLUMN
FROM child
WHERE child.parent = p.id
ORDER BY child.feature DESC
) CHILD_ROW
natguy8
Gamedev programmer currently in retirement... and yet still having great fun with games!
Updated on June 06, 2022Comments
-
natguy8 about 2 years
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...
-
Raj More over 14 yearsThis will not return just the top child every time
-
manji over 14 yearsI think grouping by (c.id, c.parent) is like returning the original table (Childs) since a parent can't have the same child with 2 features.
-
AndyH about 10 yearsOMG Ponies' method also does not work, I tested, and I get too many rows with both his snippets, as manji indicated.
-
Stijn de Witt almost 7 yearsWorking code is most important of course, but some explanation of how it works would be great.