Sql multiple column ordering

The nature of default sorting

When orchestrating a multiple column sort, it's important to understand the default behaviour. Columns are given precedence in sorting based on their order of appearance in the ORDER BY clause. In absence of ASC or DESC, the behaviour defaults to ascending sorting.

-- Beware of the name game!
SELECT FirstName, LastName FROM Users ORDER BY LastName, FirstName;


Such a query will sort entries by LastName then by FirstName, both in ascending order. FirstName is the secondary sorting attribute, kicking in only when there are identical occurrences of LastName.

Digging deep with expressions

Sometimes, you need an unconventional sorting strategy—for instance, sorting based on a computed value or a conditional result. Such scenarios call for the use of expressions in the ORDER BY clause.

-- Point out the high-scorers!
SELECT CustomerID, Points FROM LoyaltyProgram
ORDER BY (Points / TotalPurchases) DESC;


The code sorts customers based on average points earned per purchase, presented in descending order, like a leaderboard. This method is gold when tracking performance metrics or handling scenarios where simple sorting just doesn't cut it.

Let's get judgemental with CASE statements

Craving even more control over sorting? Say hello to CASE statements in ORDER BY clause, which provide a powerful way to include conditional logic in your sorting.

-- The Price is Right, or is it?
SELECT ProductName, SalePrice FROM Products
ORDER BY 
CASE WHEN SalePrice > 100 THEN 1 ELSE 0 END DESC, 
ProductName ASC;


Products priced over $100 grab the limelight while the remaining products politely queue up in alphabetical order. Such dual-layered sorting considers value priority and alphabetical sequence, enhancing data insights.

Traps & Tricks: Sorting nuances and common pitfalls

The power of complex ordering can often lead to some head-scratching moments. To avoid data misinterpretation, it's essential to understand how the ORDER BY clause impacts your dataset, especially when new rows are inserted.

For example, consider the new insertion:

-- New kid on the block!
INSERT INTO People (FirstName, YearOfBirth) VALUES ('Adam', 1990);


It changes your previous sorting:

-- Can't stop the rock!
SELECT * FROM People ORDER BY FirstName DESC, YearOfBirth ASC;


The new row is positioned according to FirstName and YearOfBirth values, underlining the fact that databases are constantly evolving entities.

Can't touch this: Compound and complex ordering

In data-heavy applications, it's often necessary to stitch multiple conditional sorts together to reflect the complexity of real-world scenarios. Here's an example of this:

-- Who's bringing returns and churning profits?
SELECT EmployeeName, Sales, Returns FROM SalesRecords
ORDER BY 
CASE WHEN Returns > 5 THEN Sales ELSE 0 END DESC, 
EmployeeName;


In this setup, salespeople clocking more than five returns are ordered by their sales figure; rest are sorted alphabetically. This approach facilitates hierarchical sorting sensitive to business rules, making data output more meaningful.