Home » SQL & PL/SQL » SQL & PL/SQL » SEQUENCE VALUE (oracle 10g)
SEQUENCE VALUE [message #577218] Wed, 13 February 2013 08:55 Go to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Hi

how can i control the sequence to return same value for repeating data.

inter_no

10
10
11
12
12
13
14

what is required is as below

inter_no seq_val

10 1
10 1
11 2
12 3
12 3
13 4
14 5

is is on select statement will will insert the data once i get the sequence val is same for repeating one

Re: SEQUENCE VALUE [message #577221 is a reply to message #577218] Wed, 13 February 2013 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Why do you consistently & repeatedly NOT comply with Posting Guideline?

http://www.orafaq.com/forum/t/88153/0/
Re: SEQUENCE VALUE [message #577226 is a reply to message #577218] Wed, 13 February 2013 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER function.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: SEQUENCE VALUE [message #577236 is a reply to message #577226] Wed, 13 February 2013 11:43 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Hi Michel,

I have to insert the record based on select i have said above, rowner will be same for different set of grouping but sequence will start after the max value.

I will send you the test case

Re: SEQUENCE VALUE [message #577259 is a reply to message #577236] Wed, 13 February 2013 15:30 Go to previous messageGo to next message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I suppose that ROW_NUMBER Michel suggested is a better choice than this, but - if that "sequence" number comes from a sequence (using sequence_name.NEXTVAL), see whether CURRVAL does any good.
Re: SEQUENCE VALUE [message #577268 is a reply to message #577236] Wed, 13 February 2013 17:49 Go to previous message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
guddu_12 wrote on Wed, 13 February 2013 18:43
Hi Michel,

I have to insert the record based on select i have said above, rowner will be same for different set of grouping but sequence will start after the max value.

I will send you the test case


You have 3 ranking functions: ROW_NUMBER, RANK and DENSE_RANK.
Have a look at http://www.orafaq.com/forum/mv/msg/160920/472554/102589/#msg_472554 post.

Waiting for the test case... (which should take you 1 minute).

Regards
Michel


Previous Topic: ORA - 00972 : identifier too long
Next Topic: Row with Subtotal
Goto Forum:
  


Current Time: Sat Oct 25 17:22:30 CDT 2014

Total time taken to generate the page: 0.10018 seconds