OVER clause with ORDER BY in SQL

Introduction

The OVER clause with ORDER BY lets you perform calculations across rows in a specific order without grouping them. It helps you see running totals, ranks, or other ordered results.

When you want to calculate a running total of sales by date.

When you want to assign ranks to students based on their scores.

When you want to find the previous or next value in a sorted list.

When you want to calculate moving averages over ordered data.

When you want to add row numbers to results sorted by a column.

Syntax

SQL

FUNCTION() OVER (ORDER BY column_name [ASC|DESC])

The FUNCTION() can be things like ROW_NUMBER(), RANK(), SUM(), etc.

The ORDER BY inside OVER defines the order for the function to work on.

Examples

This assigns a rank to each student based on their score, highest score gets rank 1.

SQL

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students;

This calculates a running total of sales ordered by date.

SQL

SELECT date, sales, SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total FROM sales_data;

This ranks employees by salary, with ties getting the same rank.

SQL

SELECT employee, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

Sample Program

This example creates a sales_data table, inserts three rows, then shows sales with a running total ordered by date.

SQL

CREATE TABLE sales_data (date DATE, sales INT);
INSERT INTO sales_data VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 120);

SELECT date, sales, SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS running_total FROM sales_data ORDER BY date;

Important Notes

The OVER clause does not reduce rows like GROUP BY; it adds calculations alongside each row.

ORDER BY inside OVER only affects the window function, not the final output order unless you add an outer ORDER BY.

Summary

The OVER clause with ORDER BY lets you do calculations over ordered rows without grouping.

Common functions used are ROW_NUMBER(), RANK(), SUM(), and others.

It is useful for running totals, rankings, and ordered calculations.