We have a family tree. Some people have children, while others do not. We want to sort results by alphabetically listing individuals with children in an initial group, and then those without children in a second group — also alphabetically. Let’s assume there is not an
isParent column (nor the equivalent) on the table, but there is a way to determine how many children each individual has.
The problem is that if we simply sort by the children count of each individual, then their name, it will provide incorrect, undesirable results. It will first show alphabetically those with the most children, then those with the second most, . . ., and finally those individuals with no children at all.
This could be accomplished with a SQL Statement like so:
1 2 3 4 5
SELECT thisPerson.lastName, thisPerson.firstName, COUNT(child.id) AS children FROM person AS thisPerson, person AS child WHERE thisPerson.id = child.parentId GROUP BY person.id ORDER BY children, thisPerson.lastName, thisPerson.firstName
INT given by the
COUNT(child.id) as children is going to mess up our sorting. Instead, we’d like it to be either a 0 or a 1 (i.e. dynamically calculate
If we think about it, the children count will always be an
INT >= 0. Knowing that, we can apply this aggregate formula to convert the count into our desired results in pseudocode:
isParent: Ceiling( children / (children + 1) )
In the case where children is 0, we’d have: Ceiling(0/(0+1)) = Ceiling(0) = 0
In the case where children is 1, we’d have: Ceiling(1/(1+1)) = Ceiling(.5) = 1
In the case where children is 2, we’d have: Ceiling(2/(2+1)) = Ceiling(.667) = 1
In the case where children is 3, we’d have: Ceiling(3/(3+1)) = Ceiling(.75) = 1
We’ll leave it as an exercise for the reader to convert the SQL statement using the above formula. Of course, ask away if you have questions.