CODEWITHSUNDEEP
phpmysqlselect
Thanh Le
Thanh Le

Reputation: 235

mySQL select top item has most child in one table?

I have a table as follows:

id    name    birthday    sex    parent

With parent is value of id, such as i have 3 records:

record 1:    1    'dad'    '1960-10-10'    0    0

record 2:    2    'son'    '2000-05-05'    0    1

record 3:    3    'daughter'   '2004-03-05'   1    1

Record 2,3 is child of record 1. How can i select top item that has most child and order by DESC. Thanks.

Upvotes: 0

Views: 295

Answers (1)

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

How can i select top item that has most child

This is a basic application of the GROUP BY clause + COUNT() aggregate function.

This will group all children together (according to parent) and count them. Finally, they are ordered according to that count:

SELECT parent, COUNT(*) AS c FROM tbl 
  WHERE parent != 0 -- ignore "no parent"
  GROUP BY(parent) 
  ORDER BY c DESC;

Upvotes: 1

Related Questions