CODEWITHSUNDEEP
sqlsqlitesortingcount
thelolcat
thelolcat

Reputation: 11625

Order parent records by child record count

I have a table that has a foreign key that points to the id column same table.

pages
=====
id      integer primary key autoincrement
name    text
parent  integer
FOREIGN KEY(parent) REFERENCES pages(id)

When I do a select query is it possible to sort the results by the number of children records ?

Upvotes: 3

Views: 688

Answers (2)

CL.
CL.

Reputation: 180210

You could compute the count of children with a correlated subquery and sort according to that:

SELECT id, name
FROM pages
ORDER BY (SELECT count(*)
          FROM pages AS p2
          WHERE p2.parent = pages.id);

Upvotes: 1

Mureinik
Mureinik

Reputation: 312008

You could join it with an aggregate query on the child records and sort according to that:

SELECT   p.*
FROM     pages p
JOIN     (SELECT   parent, COUNT(*) AS cnt
          FROM     pages
          GROUP BY parent) c ON p.id = c.parent
ORDER BY c.cnt

Upvotes: 1

Related Questions