How to sort data into groups then further sort within each group using SQL

I recently came across a complex sorting problem that required sorting the data into groups, then further sorting the data within each group.

Requirement

My requirement is to sort a list of orders for users in charge of approving orders.

Here is a list of orders with their status and department:
order-list

Here is a list of users, which have one or more departments assigned to them:
user-departments

The orders have to be sorted in the following 3 groups or 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 from newest to oldest

The order id can be used to sort from newest to oldest.

For example, for user 1020, which is assigned to the ACCOUNTING and HR departments, the sorted list should look like this:
sorted-list

Solution

Use a CASE statement in the ORDER BY to group 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;

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');

Leave a Comment