[sql] Oracle 'Partition By' and 'Row_Number' keyword

I have a SQL query written by someone else and I'm trying to figure out what it does. Can someone please explain what the Partition By and Row_Number keywords does here and give a simple example of it in action, as well as why one would want to use it?

An example of partition by:

(SELECT cdt.*,
        ROW_NUMBER ()
        OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
              ORDER BY cdt.country_code, cdt.account, cdt.currency)
           seq_no
   FROM CUSTOMER_DETAILS cdt);

I've seen some examples online, they are in bit too depth.

Thanks in advance!

This question is related to sql oracle partition row-number analytic-functions

The answer is


I know this is an old thread but PARTITION is the equiv of GROUP BY not ORDER BY. ORDER BY in this function is . . . ORDER BY. It's just a way to create uniqueness out of redundancy by adding a sequence number. Or you may eliminate the other redundant records by the WHERE clause when referencing the aliased column for the function. However, DISTINCT in the SELECT statement would probably accomplish the same thing in that regard.


That selects the row number per country code, account, and currency. So, the rows with country code "US", account "XYZ" and currency "$USD" will each get a row number assigned from 1-n; the same goes for every other combination of those columns in the result set.

This query is kind of funny, because the order by clause does absolutely nothing. All the rows in each partition have the same country code, account, and currency, so there's no point ordering by those columns. The ultimate row numbers assigned in this particular query will therefore be unpredictable.

Hope that helps...


I often use row_number() as a quick way to discard duplicate records from my select statements. Just add a where clause. Something like...

select a,b,rn 
  from (select a, b, row_number() over (partition by a,b order by a,b) as rn           
          from table) 
 where rn=1;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table?

Examples related to partition

Oracle 'Partition By' and 'Row_Number' keyword

Examples related to row-number

Trouble using ROW_NUMBER() OVER (PARTITION BY ...) How to show row number in Access query like ROW_NUMBER in SQL Oracle 'Partition By' and 'Row_Number' keyword Select specific row from mysql table MySQL - Get row number on select ROW_NUMBER() in MySQL How do I use ROW_NUMBER()? Equivalent of Oracle's RowID in SQL Server MSSQL Select statement with incremental integer column... not from a table Why is there still a row limit in Microsoft Excel?

Examples related to analytic-functions

What is ROWS UNBOUNDED PRECEDING used for in Teradata? Oracle 'Partition By' and 'Row_Number' keyword Taking the record with the max date SQL Row_Number() function in Where Clause