CODEWITHSUNDEEP
sqloracle-databaseplsqlsequences
Cool_Oracle
Cool_Oracle

Reputation: 313

Oracle sequence/number generation based on specific set of records in a select statement

I am fetching one particular result set using oracle query. In that I have a key which denotes the next set of records. I need to use the key and generate the sequence for those records. For e.g. The oracle resultset looks like this without the sequence.

PROD_ID SEQUENCE TYPE      QUANTITY
1-9ER        200 Capsule         50
1-9ER        210 Tablet          80
1-9ER        220 Tonic           75
1-9ER        230 Injection       90
1-7OR        200 Capsule         80
1-7OR        210 Tablet          30
1-7OR        220 Tonic           15
1-7OR        230 Injection       20

So, I need to embed the sequnce through SQL select query and it should increment to the next set of 100 once it finds a different product ID. So, is it possible to write a query in oracle for this? I am just using a general sequence in my query, but that does not solve my issue. Please provide me with ideas on how to resolve this.

Thank you.

Upvotes: 0

Views: 1866

Answers (1)

user330315
user330315

Reputation: 

select prod_id, 
       100 * dense_rank() over (order by prod_id) + row_number() over (partition by prod_id order by type) - 1 as seq_number,
       type,
       quantity
from products
order by prod_id, type;

SQLFiddle example: http://sqlfiddle.com/#!4/d97c6/2

Edit after the question has been changed:

You can change the way the numbers are generated, by changing the use of the window functions:

select prod_id, 
       200 + row_number() over (partition by prod_id order by type) * 10 as seq_number,
       type,
       quantity
from products
order by prod_id, type;

SQLFiddle: http://sqlfiddle.com/#!4/d97c6/3

Upvotes: 1

Related Questions