SQL – Sort into groups, then sort within groups

I recently came across a complex sorting problem that required sorting the data into groups, then further sorting the data within each group. I’ll explain this problem with a concrete example, and then I’ll show the SQL query I used to solve the problem.

Let’s say we have orders, departments, and users. Users can be assigned to multiple departments. When showing the orders to a user, they have to be sorted into the following 3 groups (buckets):

1. Orders awaiting approval for the user’s departments, from newest to oldest
2. Orders awaiting approval for other departments, from newest to oldest
3. All other orders.

Then within each group, they have to be sorted from newest to oldest (based on order id).

Here’s an example showing the sorted orders for a user who’s assigned to the HR and ACCOUNTING departments (remember, the user’s departments determine what goes into group 1):

Next, I’ll explain how to solve this problem to get these results with SQL.

Note: This doesn’t have to do with GROUP BY, which is used to aggregate rows based on a grouping condition. This problem has to do with sorting rows into groups.

Solution

Use a CASE statement in the ORDER BY to sort the data into 3 groups. Then, sort by order id descending, so that orders are displayed from newest to oldest within each group.

I’m using MySQL. The following query returns the sorted order list for user 1020.

SELECT *
FROM makolyte.orders
ORDER BY 
	CASE
		WHEN order_status = 'AWAITING_APPROVAL' 
			AND department IN 
            (SELECT department FROM user_departments WHERE user_id = '1020') THEN 1
		WHEN order_status = 'AWAITING_APPROVAL' THEN 2
		ELSE 3
	END, 
order_id DESC;
Code language: SQL (Structured Query Language) (sql)

Initial attempts:

Before coming up with the solution above, I tried the following:

• Simple ORDER BY – This doesn’t work because the first two groups require sorting by status, department, and order id, while the third group requires sorting only by order id.
• Union – Having two subqueries with different ORDER BY combined using UNION doesn’t work because a query that uses UNION is allowed an ORDER BY only at the end of the statement.

Sample Data

To try this example on your computer, run this query to create the database, tables, and sample data.

CREATE DATABASE makolyte;
USE makolyte;

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_status VARCHAR(25) NOT NULL,
    department VARCHAR(25) NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE TABLE user_departments (
    user_role_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    department VARCHAR(25) NOT NULL,
    PRIMARY KEY (user_role_id)
);

insert into orders (order_status, department) values ('APPROVED', 'ACCOUNTING');
insert into orders (order_status, department) values ('DRAFT', 'HR');
insert into orders (order_status, department) values ('AWAITING_APPROVAL', 'OPERATIONS');
insert into orders (order_status, department) values ('AWAITING_APPROVAL', 'ACCOUNTING');
insert into orders (order_status, department) values ('APPROVED', 'IT');
insert into orders (order_status, department) values ('AWAITING_APPROVAL', 'HR');
insert into orders (order_status, department) values ('APPROVED', 'HR');
insert into orders (order_status, department) values ('DRAFT', 'BILLING');
insert into orders (order_status, department) values ('AWAITING_APPROVAL', 'HR');
insert into orders (order_status, department) values ('AWAITING_APPROVAL', 'BILLING');

insert into user_departments (user_id, department) values (1010, 'IT');
insert into user_departments (user_id, department) values (1020, 'ACCOUNTING');
insert into user_departments (user_id, department) values (1020, 'HR');
insert into user_departments (user_id, department) values (1030, 'OPERATIONS');
insert into user_departments (user_id, department) values (1040, 'BILLING');

Code language: SQL (Structured Query Language) (sql)