CODEWITHSUNDEEP
oracle-databaseplsqloracle10goracle11g
Girish Acharya
Girish Acharya

Reputation: 99

Generating number

Friends I had written this code. It will generate srlnum ,lotsrl. srlnum will be total number of record and lotsrl is record from 1 to 5000 after 5000 it Will again start from 1..

 /* Formatted on 2012/12/21 15:00 (Formatter Plus v4.8.8) */
            SELECT ROW_NUMBER () OVER (ORDER BY a.co_series, a.CATEGORY,
                    a.dnr_from) srlnum,
                   CASE
                      WHEN MOD (ROW_NUMBER () OVER (ORDER BY a.co_series, a.CATEGORY,
                                 a.dnr_from),
                                5000
                               ) = 0
                         THEN 5000
                      ELSE MOD (ROW_NUMBER () OVER (ORDER BY a.co_series, a.CATEGORY,
                                 a.dnr_from),
                                5000
                               )
                   END lotsrl
              FROM iq_dnrfl a, iq_cocat b
             WHERE a.co_series = 'S1'
               --SYS_CONTEXT ('TFR_CTX', 'p$_csrcod')
               AND a.CATEGORY = 9
               --SYS_CONTEXT ('TFR_CTX', 'p$_category')
               AND a.co_series = b.co_series
               AND a.CATEGORY = b.CATEGORY
               AND NVL (olf_flag, 'N') = 'Y';

Some thing like this 

srlnum ,lotsrl
1         1
2         2
3         3 
4         4 
5         5 
.         . 
.         .
.         .
5000     5000
5001      1 
.
.
.
10000    5000
10001    1
.
.
.

But now i want another column which should generate number 1 for number between 1 to 5000 and 2 from another number between 1 to 5000.

Something like this

srlnum ,lotsrl ,num
1         1      1
2         2      1  
3         3      1
4         4      1
5         5      1
.         .      1
.         .      1
.         .      1
5000     5000    1
5001     1       2  
.        2       2
.        3       2  
.            2  
10000    5000    2
10001    1       3
.                3
.
.

Please help me friends

Upvotes: 3

Views: 112

Answers (3)

Ajith Sasidharan
Ajith Sasidharan

Reputation: 1155

I think you can try this way but since i did not have your complete data i used mine set

select a,b,
rank() over (partition by b order by a) c 
from tbl
order by a;

http://www.sqlfiddle.com/#!4/2f63a/11

Upvotes: 3

Florin Ghita
Florin Ghita

Reputation: 17643

maybe:

trunc((ROW_NUMBER () OVER (ORDER BY a.co_series, a.CATEGORY, a.dnr_from) / 5000)

Upvotes: 0

Aioros
Aioros

Reputation: 4383

Try to select this as num:

TRUNC( ROW_NUMBER () OVER (ORDER BY a.co_series, a.CATEGORY, a.dnr_from) / 5001 ) + 1

Upvotes: 0

Related Questions