The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.

In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you're de-nomalising the emp table; you still return every record in the emp table.)

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

If there was another column (e.g., state) then you could count how many departments in that State.

It is like getting the results of a GROUP BY (SUM, AVG, etc.) without the aggregating the result set (i.e. removing matching records).

It is useful when you use the LAST OVER or MIN OVER functions to get, for example, the lowest and highest salary in the department and then use that in a calculation against this records salary without a sub select, which is much faster.

Read the linked AskTom article for further details.

Answer from Guy on Stack Overflow
๐ŸŒ
DataCamp
datacamp.com โ€บ cheat-sheet โ€บ sql-window-functions-cheat-sheet
SQL Window Functions Cheat Sheet | DataCamp
October 23, 2022 - Similar to an aggregate function (GROUP BY), a window function performs the operation across multiple rows. Unlike an aggregate function, a window function does not group rows into one single row. Windows can be defined in the SELECT section of the query. SELECT window_function() OVER( PARTITION BY partition_expression ORDER BY order_expression window_frame_extent ) AS window_column_alias FROM table_name
๐ŸŒ
DataCamp
campus.datacamp.com โ€บ courses โ€บ postgresql-summary-stats-and-window-functions โ€บ introduction-to-window-functions
PARTITION BY | SQL
Enter PARTITION BY. This OVER subclause splits the table into partitions based on a column's unique values, similar to GROUP BY. Unlike GROUP BY, however, the results of a window function with PARTITION BY aren't rolled into one column. Partitions are operated on separately by the window function.
Top answer
1 of 7
304

The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.

In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you're de-nomalising the emp table; you still return every record in the emp table.)

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

If there was another column (e.g., state) then you could count how many departments in that State.

It is like getting the results of a GROUP BY (SUM, AVG, etc.) without the aggregating the result set (i.e. removing matching records).

It is useful when you use the LAST OVER or MIN OVER functions to get, for example, the lowest and highest salary in the department and then use that in a calculation against this records salary without a sub select, which is much faster.

Read the linked AskTom article for further details.

2 of 7
232

The concept is very well explained by the accepted answer, but I find that the more example one sees, the better it sinks in. Here's an incremental example:

  1. Boss says "get me number of items we have in stock grouped by brand"

You say: "no problem"

SELECT 
      BRAND
      ,COUNT(ITEM_ID) 
FROM 
      ITEMS
GROUP BY 
      BRAND;

Result:

+--------------+---------------+
|  Brand       |   Count       | 
+--------------+---------------+
| H&M          |     50        |
+--------------+---------------+
| Hugo Boss    |     100       |
+--------------+---------------+
| No brand     |     22        |
+--------------+---------------+
  1. The boss says "Now get me a list of all items, with their brand AND number of items that the respective brand has"

You may try:

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) 
 FROM 
      ITEMS
 GROUP BY 
      BRAND;

But you get:

ORA-00979: not a GROUP BY expression 

This is where the OVER (PARTITION BY BRAND) comes in:

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) OVER (PARTITION BY BRAND) 
 FROM 
      ITEMS;

Which means:

  • COUNT(ITEM_ID) - get the number of items
  • OVER - Over the set of rows
  • (PARTITION BY BRAND) - that have the same brand

And the result is:

+--------------+---------------+----------+
|  Items       |  Brand        | Count()  |
+--------------+---------------+----------+
|  Item 1      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 2      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 3      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 4      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 5      |  H&M          |   50     | 
+--------------+---------------+----------+

etc...

๐ŸŒ
DataCamp
campus.datacamp.com โ€บ courses โ€บ database-design โ€บ database-management
Table partitioning | SQL
First, you add the PARTITION BY clause to your table creation statement. You pass it the column you want to partition by, 'timestamp' in our case. Next, you have to create the partitions. To do this, use the PARTITION OF clause to create tables for the specific partitions.
๐ŸŒ
DataCamp
campus.datacamp.com โ€บ courses โ€บ data-manipulation-in-sql โ€บ window-functions-4
OVER with a PARTITION | SQL
The syntax for a partition is fairly simple. Just like before, use an aggregate function to compute a calculation, such as the AVG of the home_goal column. You then add the OVER clause afterward, and inside the parentheses, state PARTITION BY, followed by the column you want to partition the ...
๐ŸŒ
DataCamp
campus.datacamp.com โ€บ courses โ€บ data-manipulation-in-sql โ€บ window-functions-4
PARTITION BY multiple columns | SQL
Have a go at this exercise by completing this sample code. SELECT date, season, home_goal, away_goal, CASE WHEN hometeam_id = 8673 THEN 'home' ELSE 'away' END AS warsaw_location, -- Calculate average goals partitioned by season and month ___(home_goal) ___(___ ___ ___, EXTRACT(___ FROM date)) AS season_mo_home, ___(away_goal) ___(___ ___ ___, EXTRACT(___ FROM date)) AS season_mo_away FROM match WHERE hometeam_id = ___ OR awayteam_id = ___ ORDER BY (home_goal + away_goal) DESC;
๐ŸŒ
DataCamp
campus.datacamp.com โ€บ courses โ€บ data-manipulation-in-sql โ€บ window-functions-4
PARTITION BY a column | SQL
Have a go at this exercise by completing this sample code. SELECT date, season, home_goal, away_goal, CASE WHEN hometeam_id = 8673 THEN 'home' ELSE 'away' END AS warsaw_location, -- Calculate separately the average home and away goals scored, partitioned by season ___(___) ___(___ ___ ___) AS season_homeavg, ___(___) ___(___ ___ ___) AS season_awayavg FROM match -- Filter the data set for Legia Warszawa (id 8673) matches only WHERE ___ = ___ OR ___ = ___ ORDER BY (home_goal + away_goal) DESC;
๐ŸŒ
DataCamp
datacamp.com โ€บ tutorial โ€บ qualify-the-sql-filtering-statement-you-never-knew-you-needed
Master the SQL QUALIFY Statement: A Comprehensive Tutorial | DataCamp
June 5, 2023 - -- Starter code from @Jiho Choi on StackOverflow SELECT user_id, ip, country_code, os, RANK() over ( PARTITION BY user_id ORDER BY log_datetime DESC ) as previous_logins FROM login_logs WHERE TRUE QUALIFY previous_logins = 1 ยท This works solution works because QUALIFY clauses are evaluated after WINDOW functions in SQLs order of operations, which means they are aware of their existence so they can filter them in the same query.
Find elsewhere
๐ŸŒ
DB Vis
dbvis.com โ€บ thetable โ€บ sql-partition-by-in-postgresql-a-guide-to-window-functions-and-data-segmentation
SQL PARTITION BY: A Guide to Window Functions and Data Segmentation
January 28, 2025 - In SQL, window frames define the set of rows within a partition that are considered for calculation by a window function, based on a specified range or number of preceding and following rows relative to the current row.
๐ŸŒ
Experts Exchange
experts-exchange.com โ€บ articles โ€บ 32791 โ€บ Partition-By-Clause-in-Oracle.html
Partition By Clause in Oracle | Experts Exchange
October 3, 2018 - So when the boundaries are crossed then the function get restarted to segregate the data. The "partition by" clause is similar to the "GROUP BY" clause that is used in aggregate functions. They are also known as query partition clause in Oracle.
๐ŸŒ
DataCamp
datacamp.com โ€บ doc โ€บ mysql โ€บ mysql-partition-by
MySQL PARTITION BY Clauses: Usage & Examples
Learn how to use the MySQL PARTITION BY clause to enhance data analysis with window functions, enabling detailed computations like rankings and running totals within partitions.
๐ŸŒ
DataCamp
datacamp.com โ€บ tutorial โ€บ row-number-sql
ROW_NUMBER SQL Function: How to Display Row Numbers | DataCamp
June 12, 2024 - Now, let's order employees from newest to oldest hire date within their respective genders. We'll again use the ORDER BY clause to sort by hire_date, but this time in descending order (using DESC) to prioritize the most recent hires. To achieve separate numbering for each gender, we'll introduce the PARTITION BY gender clause.
๐ŸŒ
Oracle
docs.oracle.com โ€บ database โ€บ 122 โ€บ VLDBG โ€บ partition-create-tables-indexes.htm
Specifying Partitioning When Creating Tables and Indexes
View and run a related example on Oracle Live SQL at Oracle Live SQL: Creating a List Partitioned Table. ... CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2(20), quarterly_sales number(10, 2), state varchar2(2)) PARTITION BY LIST (state) (PARTITION q1_northwest VALUES ('OR', 'WA'), PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'), PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'), PARTITION q1_southeast VALUES ('FL', 'GA'), PARTITION q1_northcentral VALUES ('SD', 'WI'), PARTITION q1_southcentral VALUES ('OK', 'TX'));
๐ŸŒ
LearnSQL.com
learnsql.com โ€บ blog โ€บ partition-by-with-over-sql
How to Use the SQL PARTITION BY With OVER | LearnSQL.com
AVG(car_price) OVER (PARTITION BY car_type) AS "car type average price" The window functions are quite powerful, right? If youโ€™d like to learn more by doing well-prepared exercises, I suggest the course "Window Functions", where you can learn about and become comfortable with using window functions in SQL databases.
๐ŸŒ
Oracle
docs.oracle.com โ€บ cd โ€บ B19306_01 โ€บ server.102 โ€บ b14220 โ€บ partconc.htm
18 Partitioned Tables and Indexes
Subpartitioned indexes are always local and stored with the table subpartition by default. Tablespaces can be specified at either index or index subpartition levels. Partitioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons are: ... The Oracle database server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements.
๐ŸŒ
SQL Tutorial
sqltutorial.org โ€บ home โ€บ sql window functions โ€บ sql partition by
SQL PARTITION BY Clause
February 8, 2025 - state | job | salary | average_salary ------------+-----------+-----------+---------------- California | IT | 150000.00 | 125000.00 California | Marketing | 130000.00 | 105000.00 Texas | IT | 100000.00 | 125000.00 Texas | Marketing | 80000.00 | 105000.00Code language: SQL (Structured Query Language) (sql) Note that the query uses the ROUND() function to round the average values to numbers with two decimal places. In this example, the PARITION BY clause divides the result set by the values in the job columns. Since we have two jobs (IT & Marketing), the PARTITION BY clause divides the result set into two partitions:
๐ŸŒ
Oracle
docs.oracle.com โ€บ cd โ€บ E11882_01 โ€บ server.112 โ€บ e25523 โ€บ part_admin001.htm
Creating Partitions
The names of the subpartitions, unless you use interval-* subpartitioning, are generated by concatenating the partition name with the subpartition name in the form: ... SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME 2 FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMP_SUB_TEMPLATE' 3 ORDER BY TABLESPACE_NAME; TABLESPACE_NAME PARTITION_NAME SUBPARTITION_NAME --------------- --------------- ------------------ TS1 P1 P1_A TS1 P2 P2_A TS1 P3 P3_A TS2 P1 P1_B TS2 P2 P2_B TS2 P3 P3_B TS3 P1 P1_C TS3 P2 P2_C TS3 P3 P3_C TS4 P1 P1_D TS4 P2 P2_D TS4 P3 P3_D 12 rows selected.
๐ŸŒ
Oracle-dba-online
oracle-dba-online.com โ€บ sql โ€บ oracle_table_partition.htm
How to Create Partition Tables in Oracle
CREATE TABLE products (partno NUMBER, description VARCHAR2 (60)) PARTITION BY HASH (partno) PARTITIONS 4 STORE IN (tab1, tab2, tab3, tab4);
๐ŸŒ
GeeksforGeeks
geeksforgeeks.org โ€บ pl/sql โ€บ pl-sql-partition
PL/SQL Partition - GeeksforGeeks
July 23, 2025 - Partitioning is the database management feature that will help to improve performance, scalability, and data organization by splitting a table or index into multiple parts based on a set of rules such as date ranges or specific values.
๐ŸŒ
Readthedocs
oracle.readthedocs.io โ€บ en โ€บ latest โ€บ design โ€บ partitioning โ€บ index.html
Partitioning โ€” Oracle SQL & PL/SQL Optimization for Developers 4.0.0 documentation
The third partitioning strategy requires you to define the partition key and the number of partitions; Oracle takes care of the rest. Partition names for hash-partitioned tables are automatically generated. A table can be either partitioned by one of the three partitioning strategies or by a combination of these.