ORDER BY Inside OVER in SQL Window Functions

A finance analyst needs a report showing every payment in the system with a running total that builds up day by day. A plain SUM returns the same grand total on every row. To get a number that grows with each new payment, you need ORDER BY inside the OVER clause.

What ORDER BY Inside OVER Actually Does

When you write ORDER BY inside the [OVER clause](/learn/sql-over-clause-in-window-function), you change how the window function behaves. Instead of seeing all rows at once and returning a single aggregate, the function processes rows in the order you specify and returns the accumulated result up to and including the current row.

SELECT
  pay.payment_id,
  pay.payment_date,
  pay.amount,
  SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS running_total
FROM payments pay;

The first row in date order shows just its own amount. The second row shows the sum of the first two. Each subsequent row adds its amount to the accumulated total. The result grows as the date progresses.

Without ORDER BY inside OVER, SUM would return the same grand total on every row. The ORDER BY is what creates the accumulation.

You can also accumulate a count the same way:

SELECT
  pay.payment_id,
  pay.payment_date,
  COUNT(*) OVER (ORDER BY pay.payment_date) AS payments_so_far
FROM payments pay;

Each row now shows how many payments have occurred up to and including that date.

Combining PARTITION BY and ORDER BY Inside OVER

Adding [PARTITION BY](/learn/sql-partition-by-in-window-function) before ORDER BY inside OVER creates a running calculation that resets for each group. The function accumulates within each partition independently.

SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.total_amount,
  SUM(o.total_amount) OVER (
    PARTITION BY o.customer_id
    ORDER BY o.order_date
  ) AS customer_running_total
FROM orders o;

Each customer's running total starts at zero and accumulates through their own orders in date order. When the partition changes to a new customer, the total resets. Orders from one customer have no effect on another customer's running total.

You can partition by any column and order by any column. The combination determines both which rows belong together and how they accumulate within that group.

ORDER BY Inside OVER with Ranking Functions

Ranking functions always require ORDER BY inside OVER. The ORDER BY defines which row gets rank 1, which gets rank 2, and so on.

SELECT
  e.employee_id,
  e.department,
  e.salary,
  RANK() OVER (PARTITION BY e.department ORDER BY e.salary DESC) AS salary_rank
FROM employees e;

Within each department, employees are ranked from highest to lowest salary. The employee with the highest salary gets rank 1. ORDER BY DESC means the highest value ranks first. Change it to ASC and the lowest salary ranks first.

ROW_NUMBER, RANK, and DENSE_RANK all follow the same pattern. The ORDER BY inside OVER is not optional for these functions. Without it, the database has no basis for assigning a rank.

Two ORDER BYs in the Same Query

A single query can have ORDER BY inside OVER and ORDER BY at the end. They do different things and do not interfere with each other.

SELECT
  o.order_id,
  o.customer_id,
  o.order_date,
  o.total_amount,
  SUM(o.total_amount) OVER (
    PARTITION BY o.customer_id
    ORDER BY o.order_date
  ) AS customer_running_total
FROM orders o
ORDER BY o.order_date; -- sorts the displayed rows

The ORDER BY inside OVER controls the running total accumulation per customer. The ORDER BY at the end sorts the final output by date, which may interleave rows from different customers. Both operate independently.

How This Connects to Frame Clauses

By default, ORDER BY inside OVER applies a frame that includes all rows from the start of the partition to the current row. This is the standard behavior for running totals. You can change that frame explicitly using ROWS BETWEEN or RANGE BETWEEN, which gives you control over exactly how many rows the function looks back or forward. That is covered in the window frame lesson.

Common Mistakes

1. Expecting ORDER BY inside OVER to sort the output ORDER BY inside OVER accumulates the window function. It does not control row display order. The output may appear unsorted unless you add a separate ORDER BY at the end.

-- wrong expectation: rows may not appear sorted by payment_date
SELECT pay.payment_id, pay.payment_date, pay.amount,
  SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS running_total
FROM payments pay;

Add ORDER BY at the end of the query to control display order.

-- correct: running total accumulates by date, output also sorted by date
SELECT pay.payment_id, pay.payment_date, pay.amount,
  SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS running_total
FROM payments pay
ORDER BY pay.payment_date;

2. Omitting ORDER BY inside OVER for a running total Without ORDER BY inside OVER, SUM returns the full partition total on every row. The result is the same number repeated, not a running total.

-- wrong: returns the same grand total on every row, not a running total
SELECT pay.payment_id, pay.amount,
  SUM(pay.amount) OVER () AS running_total
FROM payments pay;

Add ORDER BY inside the OVER clause to accumulate row by row.

-- correct
SELECT pay.payment_id, pay.payment_date, pay.amount,
  SUM(pay.amount) OVER (ORDER BY pay.payment_date) AS running_total
FROM payments pay;

3. Using ORDER BY inside OVER without PARTITION BY when groups are needed Without PARTITION BY, the running total accumulates across all rows in the result. If you want it to reset per customer or per group, you must add PARTITION BY.

-- wrong: running total does not reset per customer
SELECT o.order_id, o.customer_id, o.total_amount,
  SUM(o.total_amount) OVER (ORDER BY o.order_date) AS running_total
FROM orders o;

Add PARTITION BY before ORDER BY inside OVER to reset the accumulation per group.

-- correct: running total resets for each customer
SELECT o.order_id, o.customer_id, o.total_amount,
  SUM(o.total_amount) OVER (
    PARTITION BY o.customer_id
    ORDER BY o.order_date
  ) AS running_total
FROM orders o;

Quick Recap

• ORDER BY inside OVER tells the window function to accumulate row by row in the specified sequence.
• Without ORDER BY inside OVER, aggregate window functions return the full partition total on every row.
• ORDER BY inside OVER and ORDER BY at the end of the query are independent and control different things.
• PARTITION BY combined with ORDER BY inside OVER creates a running calculation that resets per group.
• Ranking functions such as ROW_NUMBER, RANK, and DENSE_RANK require ORDER BY inside OVER to function.
• The default frame with ORDER BY inside OVER runs from the start of the partition to the current row.

SELECT
  e.employee_id,
  e.hire_date,
  e.salary,
  SUM(e.salary) OVER (ORDER BY e.hire_date) AS running_salary_total
FROM employees e;

SELECT
  e.employee_id,
  e.hire_date,
  ROW_NUMBER() OVER (ORDER BY e.hire_date) AS hire_sequence
FROM employees e;

SELECT
  e.employee_id,
  e.department,
  e.hire_date,
  e.salary,
  SUM(e.salary) OVER (PARTITION BY e.department ORDER BY e.hire_date) AS dept_running_salary
FROM employees e;

SELECT * FROM (
  SELECT
    e.employee_id,
    e.department,
    e.salary,
    RANK() OVER (PARTITION BY e.department ORDER BY e.salary DESC) AS dept_salary_rank
  FROM employees e
) ranked
WHERE dept_salary_rank = 1;

SELECT
  p.product_id,
  p.category_id,
  p.price,
  RANK() OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS price_rank_in_category
FROM products p
ORDER BY p.category_id, price_rank_in_category;