In Oracle SQL, the PARTITION BY clause is used with analytic (window) functions to divide the result set into groups or partitions, over which the analytic function is applied independently.

This is similar to how GROUP BY works in aggregate functions, but with one major difference:

GROUP BY collapses rows into one per group, whereas PARTITION BY retains all rows and just scopes the analytic function to partitions.

1. Syntax

<ANALYTIC_FUNCTION>() OVER (
  PARTITION BY column1, column2, ...
  ORDER BY column3
)

Components:

• ANALYTIC_FUNCTION() – Any analytic function like RANK(), DENSE_RANK(), ROW_NUMBER(), SUM(), etc.
• PARTITION BY – Divides the result set into subsets.
• ORDER BY – Specifies how to order rows within each partition.

2. Why Use PARTITION BY?

• To restart calculations (like rankings, sums) for each group.
• To perform cumulative totals, running averages, or windowed calculations on groups of data without aggregating or collapsing rows.
• To enhance performance and accuracy of reports and analytics.

3. Example Table: employees

CREATE TABLE employees (
  employee_id   NUMBER,
  name          VARCHAR2(50),
  department_id NUMBER,
  salary        NUMBER
);

INSERT INTO employees VALUES (1, 'Alice', 10, 5000);
INSERT INTO employees VALUES (2, 'Bob', 10, 7000);
INSERT INTO employees VALUES (3, 'Charlie', 20, 6000);
INSERT INTO employees VALUES (4, 'David', 20, 6000);
INSERT INTO employees VALUES (5, 'Eve', 10, 7000);
COMMIT;

4. Example: RANK() with and without PARTITION BY

Without PARTITION BY – One global ranking:

SELECT name, department_id, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Output:

NAME	DEPT_ID	SALARY	RANK
Bob	10	7000	1
Eve	10	7000	1
Charlie	20	6000	3
David	20	6000	3
Alice	10	5000	5

With PARTITION BY – Ranking per department:

SELECT name, department_id, salary,
       RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS dept_rank
FROM employees;

Output:

NAME	DEPT_ID	SALARY	DEPT_RANK
Bob	10	7000	1
Eve	10	7000	1
Alice	10	5000	3
Charlie	20	6000	1
David	20	6000	1

Explanation:

• RANK() restarts for each department_id.
• Within each department, salaries are ranked independently.

5. Example: Cumulative Salary per Department

SELECT name, department_id, salary,
       SUM(salary) OVER (
         PARTITION BY department_id
         ORDER BY salary
       ) AS running_salary
FROM employees;

Output:

NAME	DEPT_ID	SALARY	RUNNING_SALARY
Alice	10	5000	5000
Bob	10	7000	12000
Eve	10	7000	19000
Charlie	20	6000	6000
David	20	6000	12000

Explanation:

• Running total resets with each department_id.

6. Example: ROW_NUMBER() in Partitions

Get the first employee per department by salary:

SELECT *
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rn
  FROM employees
)
WHERE rn = 1;

Output:

NAME	DEPT_ID	SALARY	RN
Bob	10	7000	1
Charlie	20	6000	1

7. PARTITION BY with Aggregate vs Analytic

Using GROUP BY:

SELECT department_id, SUM(salary)
FROM employees
GROUP BY department_id;

This collapses rows to department level.

Using PARTITION BY:

SELECT name, department_id, salary,
       SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM employees;

This retains all rows, adds department total per row.

8. Real-World Use Case: Top 2 Salaries Per Department

SELECT *
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employees
)
WHERE rnk <= 2;

9. Notes and Best Practices

• Use PARTITION BY only with analytic/window functions (RANK, SUM, AVG, ROW_NUMBER, etc.).
• Avoid confusion with GROUP BY. They serve different purposes.
• Ensure ORDER BY is also used inside OVER() when ordering matters (e.g. RANK()).

10. Summary Table

Feature	GROUP BY	PARTITION BY
Purpose	Group rows into single output	Divide rows into groups without collapsing
Used With	Aggregate functions	Analytic (window) functions
Rows Returned	One per group	All rows
Ranking, Running Total	❌ Not possible	✅ Possible
Ordering Control	Indirect	Direct with ORDER BY inside OVER()

11. Conclusion

The PARTITION BY clause is a powerful tool in Oracle SQL for grouped analytics without data loss. Use it when you need:

• Ranking per category (e.g. department, region)
• Running totals, moving averages
• Filtering top-N per group
• Grouped calculations while keeping all original rows

It is essential for data reporting, analytics, and dashboard queries in Oracle.