Skip to main content

TSQL: Utilizing ORDER BY CASE-WHEN Statement for Multi-Column Sorting

Sample Data​

You can use this Fiddle to follow along and practice Custom Sorting with the ORDER BY CASE-WHEN statement

Create Orders table and insert sample data
-- Create Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO Orders (OrderID, OrderDate, TotalAmount) VALUES
    (1, DATEADD(DAY, -7, GETDATE()), 1200.00),
    (2, DATEADD(DAY, -2, GETDATE()), 480.00),
    (3, DATEADD(DAY, -5, GETDATE()), 550.00),
    (4, DATEADD(DAY, -35, GETDATE()), 750.00),
    (5, DATEADD(DAY, -5, GETDATE()), 1100.00),
    (6, DATEADD(DAY, -6, GETDATE()), 300.00),
    (7, DATEADD(DAY, -7, GETDATE()), 1600.00),
    (8, DATEADD(DAY, -8, GETDATE()), 90.00),
    (9, DATEADD(DAY, -10, GETDATE()), 520.00),
    (10, DATEADD(DAY, -45, GETDATE()), 950.00);

SELECT *
FROM Orders

Orders Table:

OrderIDOrderDateTotalAmount
12023-08-111200.00
22023-08-16480.00
32023-08-13550.00
42023-07-14750.00
52023-08-131100.00
62023-08-12300.00
72023-08-111600.00
82023-08-1090.00
92023-08-08520.00
102023-07-04950.00

Method 1: CASE-WHEN Statement Followed by ORDER BY​

Custom Multi-Column Sorting: CASE-WHEN Statement Followed by ORDER BY
SELECT OrderID
	,OrderDate
	,TotalAmount
	,CASE
		WHEN TotalAmount >= 1000
			THEN 1 -- High-value orders
		WHEN TotalAmount >= 500
			AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
			THEN 2 -- Mid-value orders within 30 days
		ELSE 3 -- Other orders
		END AS Category
FROM Orders
ORDER BY CASE
		WHEN TotalAmount >= 1000
			THEN 1 -- High-value orders
		WHEN TotalAmount >= 500
			AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
			THEN 2 -- Mid-value orders within 30 days
		ELSE 3 -- Other orders
		END
	,TotalAmount DESC;

Query Results

OrderIDOrderDateTotalAmountCategory
72023-08-111600.001
12023-08-111200.001
52023-08-131100.001
32023-08-13550.002
92023-08-08520.002
102023-07-04950.003
42023-07-14750.003
22023-08-16480.003
62023-08-12300.003
82023-08-1090.003

The above SQL query retrieves data from the "Orders" table and categorizes the orders based on their total amounts and order dates. It then orders the results by these categories and the total amount in descending order.

  1. SELECT OrderID, OrderDate, TotalAmount: The query selects the "OrderID," "OrderDate," and "TotalAmount" columns from the "Orders" table.

  2. CASE: A conditional expression is used to create a calculated column called "Category," which categorizes the orders based on certain criteria.

    • WHEN TotalAmount >= 1000 THEN 1: If the total amount is greater than or equal to 1000, the order is categorized as a high-value order and assigned a category value of 1.

    • WHEN TotalAmount >= 500 AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30 THEN 2: If the total amount is greater than or equal to 500 and the difference in days between the "OrderDate" and the current date (obtained using GETDATE()) is less than or equal to 30, the order is categorized as a mid-value order within the last 30 days and assigned a category value of 2.

    • ELSE 3: For orders that don't meet the above conditions, they are categorized as "Other" and assigned a category value of 3.

  3. FROM Orders: This specifies the source table as "Orders."

  4. ORDER BY CASE ... END, TotalAmount DESC: The query uses the same CASE-WHEN expression used for categorization to define the sorting order. The result set is ordered primarily based on the calculated category value (high-value, recent mid-value, or other), and within each category, orders are sorted in descending order based on their total amounts.

In summary, this query categorizes orders into three groups based on their total amounts and order dates, and then sorts them based on the specified categories and the total amount in descending order. This approach allows for a flexible and dynamic sorting strategy that can prioritize orders based on different criteria.

Method 2: CASE-WHEN Statement in SELECT​

SELECT OrderID
	,OrderDate
	,TotalAmount
	,CASE
		WHEN TotalAmount >= 1000
			THEN 1 -- High-value orders
		WHEN TotalAmount >= 500
			AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30
			THEN 2 -- Mid-value orders within 30 days
		ELSE 3 -- Other orders
		END AS Category
FROM Orders
ORDER BY Category
	,TotalAmount DESC;

Query Results

OrderIDOrderDateTotalAmountCategory
72023-08-111600.001
12023-08-111200.001
52023-08-131100.001
32023-08-13550.002
92023-08-08520.002
102023-07-04950.003
42023-07-14750.003
22023-08-16480.003
62023-08-12300.003
82023-08-1090.003

In the query above, column alias is used to provide more descriptive name for the output column Category, making the results easier to understand.

  1. SELECT:

    • OrderID: This is the unique identifier for each order.
    • OrderDate: This represents the date of the order.
    • TotalAmount: This is the total cost of each order.

  2. CASE:

    • WHEN TotalAmount >= 1000 THEN 1: If the total amount is greater than or equal to 1000, it is categorized as a high-value order and assigned the category value 1.
    • WHEN TotalAmount >= 500 AND DATEDIFF(DAY, OrderDate, GETDATE()) <= 30 THEN 2: If the total amount is greater than or equal to 500 and the order was placed within the last 30 days, it falls into the mid-value category (2).
    • ELSE 3: If the order doesn't meet the above criteria, it is categorized as an "Other" order (category 3).

  3. AS Category: The calculated column is assigned the name "Category" using the AS keyword, which serves as an alias for the calculated column.

  4. FROM Orders: This specifies that the data is being retrieved from the "Orders" table.

  5. ORDER BY Category, TotalAmount DESC:

    • ORDER BY Category: The results are ordered based on the calculated "Category" column, grouping orders into high-value (1), mid-value (2), and other (3) categories.
    • TotalAmount DESC: Within each category, orders are sorted in descending order based on their total amounts.

By using the column alias "Category" in the ORDER BY clause, the query becomes more succint and comprehensible.