Convert INT to BOOLEAN in a SQL SELECT for Multi-Tiered Sorting

john.kingTechnical Tips1 Comment

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”

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *