Oracle Partition By and Row Number keyword

Question

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

User · Answer

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

User · Answer

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

User · Answer

PARTITION BY segregate sets  this enables you to be able to work ROW NUMBER   COUNT   SUM   etc  on related set independently   In your query  the related set comprised of rows with similar cdt country code  cdt account  cdt currency  When you partition on those columns and you apply ROW NUMBER on them  Those other columns on those combination set will receive sequential number from ROW NUMBER  But that query is funny  if your partition by some unique data and you put a row number on it  it will just produce same number  It s like you do an ORDER BY on a partition that is guaranteed to be unique  Example  think of GUID as unique combination of cdt country code  cdt account  cdt currency   newid   produces GUID  so what shall you expect by this expression    select    hi ho     row number   over partition by newid   order by hi ho  from tbl       Right  all the partitioned none was partitioned  every row is partitioned in their own row  rows  row numbers are all set to 1  Basically  you should partition on non-unique columns  ORDER BY on OVER needed the PARTITION BY to have a non-unique combination  otherwise all row numbers will become 1  An example  this is your data   create table tbl hi varchar  ho varchar    insert into tbl values   A   X      A   Y      A   Z      B   W      B   W      C   L      C   L      Then this is analogous to your query   select    hi ho     row number   over partition by hi ho order by hi ho  from tbl    What will be the output of that   HI  HO  COLUMN 2 A   X   1 A   Y   1 A   Z   1 B   W   1 B   W   2 C   L   1 C   L   2   You see thee combination of HI HO  The first three rows has unique combination  hence they are set to 1  the B rows has same W  hence different ROW NUMBERS  likewise with HI C rows   Now  why is the ORDER BY needed there  If the previous developer merely want to put a row number on similar data  e g  HI B  all data are B-W  B-W   he can just do this   select    hi ho     row number   over partition by hi ho  from tbl    But alas  Oracle and Sql Server too  doesn t allow partition with no ORDER BY  whereas in Postgresql  ORDER BY on PARTITION is optional  http   www sqlfiddle com   1 27821 1  select    hi ho     row number   over partition by hi ho  from tbl    Your ORDER BY on your partition look a bit redundant  not because of the previous developer s fault  some database just don t allow PARTITION with no ORDER BY  he might not able find a good candidate column to sort on  If both PARTITION BY columns and ORDER BY columns are the same just remove the ORDER BY  but since some database don t allow it  you can just do this   SELECT cdt            ROW NUMBER            OVER  PARTITION BY cdt country code  cdt account  cdt currency               ORDER BY newid               seq no    FROM CUSTOMER DETAILS cdt   You cannot find a good column to use for sorting similar data  You might as well sort on random  the partitioned data have the same values anyway  You can use GUID for example you use newid   for SQL Server   So that has the same output made by previous developer  it s unfortunate that some database doesn t allow PARTITION with no ORDER BY  Though really  it eludes me and I cannot find a good reason to put a number on the same combinations  B-W  B-W in example above   It s giving the impression of database having redundant data   Somehow reminded me of this  How to get one unique record from the same list of records from table  No Unique constraint in the table  It really looks arcane seeing a PARTITION BY with same combination of columns with ORDER BY  can not easily infer the code s intent   Live test  http   www sqlfiddle com   3 27821 6    But as dbaseman have noticed also  it s useless to partition and order on same columns   You have a set of data like this   create table tbl hi varchar  ho varchar    insert into tbl values   A   X      A   X      A   X      B   Y      B   Y      C   Z      C   Z      Then you PARTITION BY hi ho  and then you ORDER BY hi ho  There s no sense numbering similar data  -  http   www sqlfiddle com   3 29ab8 3  select    hi ho     row number   over partition by hi ho order by hi ho  as nr from tbl    Output   HI  HO  ROW QUERY A A   X   1 A   X   2 A   X   3 B   Y   1 B   Y   2 C   Z   1 C   Z   2   See  Why need to put row numbers on same combination  What you will analyze on triple A X  on double B Y  on double C Z   -     You just need to use PARTITION on non-unique column  then you sort on non-unique column s  s unique-ing column  Example will make it more clear   create table tbl hi varchar  ho varchar    insert into tbl values   A   D      A   E      A   F      B   F      B   E      C   E      C   D     select    hi ho     row number   over partition by hi order by ho  as nr from tbl    PARTITION BY hi operates on non unique column  then on each partitioned column  you order on its unique column ho   ORDER BY ho  Output   HI  HO  NR A   D   1 A   E   2 A   F   3 B   E   1 B   F   2 C   D   1 C   E   2   That data set makes more sense  Live test  http   www sqlfiddle com   3 d0b44 1  And this is similar to your query with same columns on both PARTITION BY and ORDER BY    select    hi ho     row number   over partition by hi ho order by hi ho  as nr from tbl    And this is the ouput   HI  HO  NR A   D   1 A   E   1 A   F   1 B   E   1 B   F   1 C   D   1 C   E   1   See  no sense   Live test  http   www sqlfiddle com   3 d0b44 3    Finally this might be the right query   SELECT cdt         ROW NUMBER         OVER  PARTITION BY cdt country code  cdt account -- removed  cdt currency            ORDER BY                 -- removed  cdt country code  cdt account                  cdt currency  -- keep         seq no FROM CUSTOMER DETAILS cdt

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to partition

Examples related to row-number

Examples related to analytic-functions