Analytical SQL Tips Series — ORDER BY ALL

Last week, we delved into the topic of GROUP BY ALL. This week, let’s explore a similar concept called ORDER BY ALL.

The ORDER BY ALL feature is designed to streamline the sorting process in SQL queries, making it easier and more intuitive to order your results. Traditionally, specifying sort order in SQL requires listing each column in the ORDER BY clause:

SELECT column1, sum(column2) 
FROM your_table 
GROUP BY column1 
ORDER BY column1 , sum(column2);

ORDER BY ALL aims to remove this step. Instead of manually specifying which columns to sort by, ORDER BY ALL would automatically sort the query results based on the sequence of columns in the SELECT clause (left to right):

SELECT column1, sum(column2) 
FROM your_table 
GROUP BY column1 
ORDER BY ALL 
-- ORDER BY column1, sum(column2)

ORDER BY ALL vs. ORDER BY 1 , 2

We can delve deeper into why ORDER BY ALL could be more advantageous than the traditional ORDER BY 1, 2. This is because, in practice, we often adjust the order of items in the SELECT clause or modify the selection by adding or removing items. Such adjustments can become cumbersome with ORDER BY 1, 2, as every change necessitates a corresponding update in the ORDER BY clause to maintain the intended sort order.

However, ORDER BY ALL is not without its limitations. When using ORDER BY ALL, you sacrifice the ability to specify sorting directions (ascending or descending) for individual columns. With ORDER BY ALL, any specified sorting order, whether ascending or descending, applies to all columns collectively. For instance, you cannot achieve mixed sorting directions, such as ORDER BY 1 DESC, 2 ASC, with ORDER BY ALL DESC; the sorting direction you choose applies universally to all selected columns.

How to use ORDER BY ALL

Try it out in our demo

Try it on DuckDB WASM . It takes about 10 seconds to initialize DuckDB and create the table.

CREATE TABLE Weather AS 
FROM 'https://recce-dbt-tips.s3.amazonaws.com/central-park-weather.csv';

SELECT STATION,
        NAME,
        AVG(TMAX) AS average_max_temp,
        AVG(TMIN) AS average_min_temp
FROM Weather
GROUP BY ALL 
ORDER BY NAME , average_max_temp , average_min_temp ;

SELECT STATION,
        NAME,
        AVG(TMAX) AS average_max_temp,
        AVG(TMIN) AS average_min_temp
FROM Weather
GROUP BY ALL 
ORDER BY ALL;

You can apply these SQL tip in your dbt code review. For more about analytical SQL tips, follow Recce on Linkedin.

More from In The Pipeline

dbt best practices in action at Cal-ITP’s data-infra project

How to use column value diff for data impact assessment in dbt with Recce