CODEWITHSUNDEEP
sqloracle-database
112233
112233

Reputation: 31

Generate a rownumber and repeat every five customers in oracle

How to generate a row number for every 5 customers in oracle? For example, if number Of records = 10 the expected output would be: 1..5,1..5

Like this:

CustomerName  Row Number  
A                 1  
B                 2  
C                 3  
D                 4  
E                 5  
F                 1  
G                 2  
H                 3  
I                 4  
J                 5

Upvotes: 0

Views: 1957

Answers (6)

Nikhil
Nikhil

Reputation: 3950

this will do:

select e.*, mod(rownum-1,5)+1 rownumber
from (select * from table_name) e;

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

You may use modular arithmetical logic as below :

select "Customer Name", replace(mod(rn,5),0,5) "Row Number"
  from
(
  select CustomerName as "Customer Name", row_number() over (order by CustomerName) as rn
    from
  (
    select chr(level+64) CustomerName, level as nr
      from dual
     connect by level <= 10 
  )
);

Rextester Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270693

In Oracle, you can just do:

select t.*, 1 + mod(rownum - 1, 5) as rownumber
from t;

You can replace rownum with row_number() over (order by . . . ) as well.

Upvotes: 1

Peter
Peter

Reputation: 1206

one solution would be the NTILE analytic function: https://docs.oracle.com/database/121/SQLRF/functions127.htm#SQLRF00680

It also deals nicely with cases where the number of customers is not divisible by 5 (e.g. 12 customers).

Sample: 

with customers as (        
    select level customername from dual connect by level <= 10)
select customername, ntile(5) over (order by customername asc) rownumber
from customers;

Upvotes: 3

BriteSponge
BriteSponge

Reputation: 1054

My solution uses ROW_NUMBER to assign a value to each row and then applies the MOD function to split it into 5's. Whilst this works I think the other solution using NTILE is cleaner.

WITH cust AS
  (SELECT customername, ROW_NUMBER() OVER(ORDER BY customer_name) AS ordering
   FROM customers)
SELECT customername , CASE WHEN MOD(ordering,5) = 0 THEN 5 ELSE MOD(ordering,5) END AS bucket
  FROM cust;

Upvotes: 0

Nizam - Madurai
Nizam - Madurai

Reputation: 322

this also works (oracle); alter this according to your needs

select

'data' data_column,

rownum original_rownum,

replace(mod(rownum,5),0,5) expected_rownum

from

your_table;

Upvotes: 0

Related Questions