Multiple level ordering

Complex ordering with LIMIT

At times, you may need to limit results before applying additional sorting criteria. In SQLite, complex multi-level ordering can be implemented using nested SQL commands:

-- SQLite version of inception. A query within a query! 🤯
SELECT * FROM ( 
  SELECT * FROM employees 
  ORDER BY rating DESC 
  LIMIT 20
) AS sub_query 
ORDER BY name ASC;


The above query does the following:

1. The inner query (or is it the dream within a dream?) sorts employees by their rating scores in descending order and limits the output to the top 20 entries.
2. The outer query then organizes these shortlisted 20 records further by arranging them in ascending order of names.

This strategy achieves a two-tier sorting effect and ensures you get a highly tailored and specific subset of data.

Compound ordering in multi-table situations

When working with complex datasets encompassing multiple joined tables or when multiple attributes need to be considered for sorting, nested SQL commands or Common Table Expressions (CTEs) can provide a reliable solution for sophisticated multi-level ordering:

-- This is like entering level 3 in our Inception query. I hope you're keeping up...
WITH RankedEmployees AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
  FROM employees
)
SELECT * FROM RankedEmployees
WHERE rank <= 5
ORDER BY department, rank;


Useful techniques featured in this scenario:

• CTEs: for a cleaner and more organized approach to multi-tiered sorting.
• ROW_NUMBER(): to assign ranks to employees within their respective departments based on descending order of salaries.
• PARTITION BY: to apply the sorting rules individually to each department subgroup.
• The WHERE clause is used further to filter out only the top 5 earners from each department.

Dynamic ordering with CASE statement

In SQL, the CASE statement offers dynamic sorting capabilities when certain conditions need to be accounted for in the sorting process:

-- Not all conditions are created equal! 🏆
SELECT col1, col2, col3 FROM table
ORDER BY CASE WHEN condition THEN 1 ELSE 2 END, col2 ASC;


The CASE statement here determines the sort ordering based on whether a specific condition is met: entries that satisfy the condition are prioritized and arranged before others. Sorting by col2 only comes into play after this.

Handling TIMESTAMPs in sorting

When working with TIMESTAMP data, especially those sans time zones, you may need to sort by date alone ignoring the time component:

-- Who needs time when you have dates? 📅
SELECT col1, col2, DATE(col3) as DateOnly
FROM table
ORDER BY DATE(col3), col1;


Here, entries are sorted based on date extracted from a timestamp, leaving out the time. This is a neat trick to know for handling timestamps!