The Challenge

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.

The Solution

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

The 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 isParent).

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.

Related Information:

1. Semantics, Reality, Marketing: I’m “Cloud Computing” Too

   If there were a buzz word that could compete with the speed to fame, breadth of use (and misuse), and...

2. FreeQL - Free Oracle SQL Select Statements

   M&S Consulting has begun a revolutionary service where we will write Oracle SQL statements for FREE. We have named this...