T-SQL Tutorial - How to create a custom sort order

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

In this video I talk through how you can change the default sort order on SQL Server, Azure SQL Database or Azure Synapse Analytics, perhaps you want to sort NULLs last in a query or sort data based on another column that doesn't exist in the database and you don't have permission to add. The best thing is you can follow along with this tutorial.

Remember just because you can sort a query results, doesn't mean you should, generally sorting is an expensive but there are cases when sorting is required, I recently had to create a custom sort order myself when creating a window function and this tutorial will help with those scenarios.

T-SQL Scripts:
IF OBJECT_ID(N'dbo.Account', N'U') IS NOT NULL
DROP TABLE dbo.Account;

CREATE TABLE dbo.Account
(
AccountId INT IDENTITY(1, 1)
CONSTRAINT PK_Account_AccountId PRIMARY KEY (AccountId),
OwnerId INT,
[Date] DATETIME2,
[Value] DECIMAL(6, 2)
);

INSERT INTO dbo.Account (OwnerId, [Date], [Value])
VALUES
(1, '20220401', NULL),
(1, '20220402', NULL),
(1, '20220403', 100.00),
(1, '20220404', 100.00),
(1, '20220405', 110.00),
(1, '20220406', 120.00),
(1, '20220407', 120.00),
(1, '20220408', 130.00);

SELECT
AccountId,
OwnerId,
[Date],
[Value]
FROM dbo.Account;

IF OBJECT_ID(N'dbo.Product', N'U') IS NOT NULL
DROP TABLE dbo.Product;

CREATE TABLE dbo.Product
(
ProductId INT IDENTITY(1, 1)
CONSTRAINT PK_Product_ProductId,
Category VARCHAR(50),
Product VARCHAR(50),
Price DECIMAL(6, 2)
);

INSERT INTO dbo.Product (Category, Product, Price)
VALUES
('Electronics', 'Keyboard', 199.99),
('Stationery', 'Notepad', 4.99),
('Electronics', 'Laptop', 3.99),
('Stationery', 'Pen Set', 7.00),
('Clothing', 'Jumper', 40.99),
('Clothing', 'Trousers', 32.00),
('Homeware', 'Kettle', 29.99),
('Homeware', 'Toaster', 39.99);

SELECT
*
FROM dbo.Product;