How do I sort parent record based on child record values

Ephraim Wangoya
Ephraim Wangoya used Ask the Experts™
I need to sort parent records based on the child records, consider the tables
Parent table
ID   Name
1    One
2    Two
3    Three

Child Table
ParentID  Name
1         A
1         D
1         F
2         A
2         G
2         E
3         A
3         Z
3         B
3         Q

Result Sorted
ID   Name      [Child Records]
3    Three           A, B, Q, Z
1    One           A, D, F
2    Two            A, E, G

Sort is only necessary up to three child records but not limited
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Hello ewangoya,

If the Child table is laid out as above, then with respect, that is a downright awful design.


Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
This would do:
SELECT t1.ID, t1.Name, t2.Name
FROM Parent_table t1 INNER JOIN 
(SELECT a1.ParentID, 
SELECT a2.Name + ', ' 
FROM Child_table a2 
WHERE a1.ParentID = a2.ParentID
ORDER BY a2.Name
FOR XML PATH('')), 3, 1000)
FROM Child_table a1
GROUP BY a1.ParentID) t2 on t1.ID = t2.ParentID

Open in new window

Top Expert 2011
select p.*
  from parent as P
  left outer join (select parentid,min(name) as name from child
                        group by parentid ) as c1
  left outer join child as c2
  and >
  left outer join child as c3
  and >
  where ( is null or = (select min(name) from child as x
                                            where x.parentid=c1.parentid
   and ( is null or = (select min(name) from child as x
                                            where x.parentid=c1.parentid
Ephraim WangoyaSoftware Engineer


Both the solutions are pretty good, the only draw back with rrjegan17's solution is that its only applicable to MSSSQL Server2005 onwards where as lowfatspread's solution is applicable to most databases
Thanks for the great feed back

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial