Sort records based on multiple columns' values in SQL

Use Case description:

Scenario:

We have multiple columns(in one or more tables), these columns are mutually exclusive i.e only one of these columns can have a non-null value at a given time and others will be null.

Table Schema Description

• We have a tasks table
• Table attributes are: id, task_owner, task_type
• Only one of the attributes(task_owner, task_type can have a non-null value at a given time).

Our Goal:

Our goal is to sort the records based on the values of all these columns i.e The two columns task_owner and task_type should behave as single data source, and we should be able to sort them based on the values present in this data source.

Tasks table

Expected output

Note the ordering of the records, the records are sorted based on the values of the two columns task_owner and task_type.

Solution

The solution is a simple one, we're going to use a generated/computed column which we'll use as a data source for sorting purposes.
The COALESCE operator, returns the first non-null value from its arguments.

Solution SQL

SELECT *, COALESCE(tasks.task_owner, tasks.task_type) AS sorting_col
FROM tasks
ORDER BY sorting_col;

SQL Script for Testing

• You can run & test this at PG-SQL, it lets you create tables and run SQL queries on them.

Create Table

CREATE TABLE tasks (id int, task_owner varchar(255), task_type varchar(255));

Populate Table with data

INSERT INTO tasks (id, task_owner, task_type) VALUES
(1, 'Masroor', null),
(2, null, 'Manager'),
(3, 'Alex', null),
(4, 'Yuri', null),
(5, null, 'Supervisor'),
(6, 'David', null),
(7, 'Brendon', null);