The ORDER BY Clause in SQL Window Functions

In this article, you’ll get an overview of ORDER BY in window functions. You’ll learn how it compares to PARTITION BY and the regular ORDER BY all accompanied by examples and explanations.

If you want to create reports that go beyond simple aggregations, you’ll need SQL window functions. Window functions help you create rankings, compute running totals and moving averages, and find the difference between rows. To use window functions effectively, you must understand the role of the ORDER BY clause. Not only does it change the behavior of window functions, but some window functions won’t even run without ORDER BY.

This article is for those who already have a general understanding of window functions. If you’re not familiar with them, I strongly recommend taking our Window Functions course. It will give you comprehensive knowledge of window functions, including ranking functions, analytics functions, and the ORDER BY and PARTITION BY clauses. The course features 218 coding challenges waiting to be solved; you can find more in the Window Functions Practice Set.

Even if you know SQL window functions, you may want to keep our Window Functions Cheat Sheet close by for quick reference.

What Are Window Functions?

Window functions in SQL perform operations on a window frame, which consists of the current row and the rows related to it. Unlike aggregate functions, window functions don’t collapse individual rows; instead, they add a column to each row with the function result. This means window functions enable you to see individual and aggregate data simultaneously.

Syntax

The syntax of SQL window functions is shown below:

window_function OVER ([PARTITION BY column_name] [ORDER BY column_name ASC|DESC])

Here’s a brief description of each part of the syntax:

• window_function: The window function you want to use.
• OVER(): A mandatory clause for creating a window function.
• PARTITION BY: An optional clause that partitions (divides) the data.
• ORDER BY: An optional clause that sorts the data within the window frame.

What Is the ORDER BY Clause in Window Functions?

ORDER BY  (along with PARTITION BY) is a fundamental part of many window functions. ORDER BY in the window function sorts the rows within the window frame. It defines the order in which the window function computation will be performed.

ORDER BY can sort data within a window ascendingly (A to Z, 1 to 10) or descendingly (Z to A, 10 to 1). You can sort text data in alphabetical or reverse alphabetical order, numerical data from lowest to highest (or vice versa), and date/time data from oldest to newest (or newest to oldest).

Example: ORDER BY with a Ranking Window Function

Let’s look at an example of how ORDER BY in window functions influences query execution.

The query below ranks data in the table product_sales by sales in descending order – i.e., from the highest to the lowest sales.

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales DESC) AS ranking
FROM product_sales; 

DENSE_RANK() is one of the ranking window functions. It ranks the whole dataset from the highest to the lowest sales, which is specified in ORDER BY.

The original product_sales table looks like this:

Therefore, the code above will rank rows in the dataset descendingly: from highest to lowest.

This is the query output, with the ranks shown in a separate column:

Now, the ranking would be significantly different if you replaced DESC with ASC in ORDER BY, as shown below:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(ORDER BY sales ASC) AS ranking
FROM product_sales; 

Now the ranking is performed from the lowest to the highest sales:

ORDER BY and PARTITION BY in Window Functions

We know what ORDER BY does in window functions. What about PARTITION BY? This is an optional clause that splits data into subsets based on one or more categories. We do this by specifying columns in the PARTITION BY clause like this: PARTITION BY product_name. I will demonstrate how this works in the following section.

Without PARTITION BY, ORDER BY sorts the data in the whole result set. But use it with PARTITION BY and it will sort the data within each partition separately.

Example: Using ORDER BY with PARTITION BY in a Window Function

When used with PARTITION BY, ORDER BY sorts the data within each partition.

Using ORDER BY with the SUM() window function produces a cumulative sum (i.e. the sum of the current row value plus all the rows before it in the partition). For example, the code below calculates the cumulative sum of sales by product name (as specified in PARTITION BY) from the oldest to the latest date (as specified in ORDER BY).

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name ORDER BY date ASC) AS cumulative_sum
FROM product_sales; 

The query will first split the dataset into partitions by product name. Then the values within each partition will be sorted from the oldest to the newest date, which is specified in ORDER BY. Then the SUM() window function will calculate the cumulative total by summing the current date’s sales with the sales of all the previous sales within the partition.

Here’s the output, which shows each row and the cumulative sales for each product:

If we remove ORDER BY from the code, as shown below …

SELECT 
  id,
  date,
  sales,
  product_name,
  SUM(sales) OVER(PARTITION BY product_name) AS cumulative_sum
FROM product_sales; 

…  the code will return the total sales sum for each product. In other words, by simply omitting ORDER BY from a window function, you lose the ability to calculate cumulatively. Without ORDER BY, all rows in the partition make the window frame.

ORDER BY in Window Functions vs. Regular ORDER BY

When I say regular, I mean the standard ORDER BY at the end of the query. How is it different from ORDER BY in a window function?

A regular ORDER BY sorts a query output, while ORDER BY in window functions sorts a data window or a data partition.

Example: ORDER BY in Window Functions and Regular ORDER BY

This query uses the DENSE_RANK() window function with PARTITION BY and ORDER BY to rank sales dates for each product:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS day_rank
FROM product_sales;

You can see that the overall output is not sorted from the highest to the lowest sales. The descending sorting of the dates is applied only within each partition.

If you want your output to be sorted descendingly by sales, you have to do that explicitly by adding ORDER BY at the end of the query:

SELECT 
  id,
  date,
  sales,
  product_name,
  DENSE_RANK() OVER(PARTITION BY product_name ORDER BY sales DESC) AS sales_rank_by_product
FROM product_sales
ORDER BY sales DESC;

Now you get the output sorted by sales and date. You can see how a date’s sales rank compares to other dates’ sales of the same product.

Window Functions That Require ORDER BY

As you saw in the previous example, window functions can work without ORDER BY. ORDER BY is generally considered an optional clause in window functions.

But even though ORDER BY in window functions is viewed as an optional clause, some window functions require ORDER BY to work at all. In those cases, ORDER BY becomes mandatory; those window functions require a sort order to work correctly. These window functions are:

• RANK()
• DENSE_RANK()
• NTILE()
• LEAD()
• LAG()

Default Window Frames With and Without ORDER BY

A window function’s behavior does change depending on whether it’s written with or without ORDER BY. More precisely, the presence or absence of ORDER BY impacts the default window frame.

If there’s no ORDER BY, the default window frame includes the current row and all the rows preceding and following it. In other words, all rows in the partition are included. We have seen this with the cumulative sum example: without ORDER BY, the window frame defaults to the whole partition and the sum becomes the total sum.

If there is ORDER BY, the window frame includes the current and all the preceding values. We have seen this with the cumulative sum example: with ORDER BY, the window frame includes all rows preceding the current row and the current row.

If you don’t like the default window frame, you can define it explicitly using the ROWS and RANGE keywords.

Using ORDER BY in Window Functions

There you have it: ORDER BY – sometimes mandatory, sometimes optional – is the clause that makes and occasionally breaks window functions.

However, knowing all about ORDER BY in window functions isn’t worth anything if you can’t use it in your queries. So, test what you learned here by solving the coding challenges in our Window Function course and Window Functions Practice Set.

For even more exercises, solve these 11 window function exercises and answer the top 10 window function interview questions.  Happy learning!