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.
One Comment on “Convert INT to BOOLEAN in a SQL SELECT for Multi-Tiered Sorting”
SELECT CEIL(COUNT(child.id)/(COUNT(child.id)+1)) AS isParent
FROM person AS thisPerson, person AS child
WHERE thisPerson.id = child.parentId
GROUP BY person.id
-or-
SELECT thisPerson.lastName, thisPerson.firstName, COUNT(child.id) AS children, CEIL(COUNT(child.id)/(COUNT(child.id)+1)) AS isParent
FROM person AS thisPerson, person AS child
WHERE thisPerson.id = child.parentId
GROUP BY person.id
ORDER BY children, thisPerson.lastName, thisPerson.firstName