Home » SQL & PL/SQL » SQL & PL/SQL » same sequence number for repeating data (oracle 10)
same sequence number for repeating data [message #577857] Thu, 21 February 2013 05:13 Go to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Dear All

I want to insert same sequence number for repeated data else new sequence.

my data is as below
EMP_NAME	EMP_INV

Pras	CTI
Pras	CTI
Shariq	Witness
Kate	CTI
Shariq	Assisted
Shariq	Witness



the first 2 record are same Pras CTI and Pras CTI so i want my sequence generate value once as below
1 Pras CTI
1 Pras CTI
2 Shariq Witness
3 Kate CTI
4 Shariq Assisted
2 Shariq Witness

Shariq and witness are repeating so same sequence number but Kate CTI occured once so nextval of sequence

How can i acivee it , any help will be appriciated


CREATE TABLE EMP_INV
(
  EMP_NAME  VARCHAR2(100 BYTE),
  EMP_INV   VARCHAR2(100 BYTE)
)

Insert into EMP_INV
   (EMP_NAME, EMP_INV)
 Values
   ('Shariq', 'Witness');
Insert into EMP_INV
   (EMP_NAME, EMP_INV)
 Values
   ('Shariq', 'Assisted');
Insert into EMP_INV
   (EMP_NAME, EMP_INV)
 Values
   ('Kate', 'CTI');
Insert into EMP_INV
   (EMP_NAME, EMP_INV)
 Values
   ('Shariq', 'Witness');
Insert into EMP_INV
   (EMP_NAME, EMP_INV)
 Values
   ('Pras', 'CTI');
Insert into EMP_INV
   (EMP_NAME, EMP_INV)
 Values
   ('Pras', 'CTI');
COMMIT;
Re: same sequence number for repeating data [message #577859 is a reply to message #577857] Thu, 21 February 2013 05:33 Go to previous messageGo to next message
pointers
Messages: 351
Registered: May 2008
Senior Member
How about using a rank analytical function.

SELECT emp_name, emp_inv, RANK () OVER (ORDER BY emp_name, emp_inv)
  FROM EMP_INV


Regards,
Pointers
Re: same sequence number for repeating data [message #577860 is a reply to message #577857] Thu, 21 February 2013 05:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
How data is inserted into the table? I mean from UI manually or from one table to other?

regards,
Delna
Re: same sequence number for repeating data [message #577861 is a reply to message #577860] Thu, 21 February 2013 05:39 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
Data is inserted by plsql job and rank will not solve the purpose as i want sequence value but rank will repeat for other set of data, i mean for the first record rank is 1 but for different set of record rank will start from once again 1 that is incorrect and this is incremental load
Re: same sequence number for repeating data [message #577862 is a reply to message #577861] Thu, 21 February 2013 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must have something that allows you to order the rows, otherwise it is not possible.

Regards
Michel


Re: same sequence number for repeating data [message #577865 is a reply to message #577862] Thu, 21 February 2013 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 19764
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Assuming that sequence in your first message is just an example (because you didn't say how to order these records), here's an example; see whether you can adjust it according to your needs.
SQL> select
  2    dense_rank() over (order by emp_name, emp_inv) rb,
  3    emp_name,
  4    emp_inv
  5  from emp_inv
  6  order by emp_name, emp_inv;

        RB EMP_NAME   EMP_INV
---------- ---------- --------------------
         1 Kate       CTI
         2 Pras       CTI
         2 Pras       CTI
         3 Shariq     Assisted
         4 Shariq     Witness
         4 Shariq     Witness

6 rows selected.

SQL>


P.S. Huh, didn't even see Pointers' message; though, he used RANK as opposed to my DENSE_RANK (which, I suppose, returns somewhat "better" result).

[Updated on: Thu, 21 February 2013 05:53]

Report message to a moderator

Re: same sequence number for repeating data [message #577867 is a reply to message #577865] Thu, 21 February 2013 05:58 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
the data can be group by emp_name, emp_inv. but i can't use rank because the tale can go under update. i read from the above table and insert into other table with sequence number. other table has same structure just additional column seq_num. why i can't use rank is if i run the load again for new record it will have generate rank within the group but i want it to generate new sequnce number.
Re: same sequence number for repeating data [message #577869 is a reply to message #577867] Thu, 21 February 2013 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still didn't say how to order the rows.

Regards
Michel
Re: same sequence number for repeating data [message #577873 is a reply to message #577869] Thu, 21 February 2013 06:18 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
you can order the by column emp_name, emp_inv , they formed the uniq number and once you load with sequence so all the 3 column will have unique data that is why i don't want rank.
Re: same sequence number for repeating data [message #577876 is a reply to message #577861] Thu, 21 February 2013 06:57 Go to previous messageGo to next message
pointers
Messages: 351
Registered: May 2008
Senior Member
Quote:
Data is inserted by plsql job and rank will not solve the purpose as i want sequence value but rank will repeat for other set of data, i mean for the first record rank is 1 but for different set of record rank will start from once again 1 that is incorrect and this is incremental load

Here we are not using the option of partition, so there is no chance for setting the value to 1
See the output of this.

SELECT emp_name, emp_inv, RANK () OVER (ORDER BY emp_name, emp_inv)
  FROM EMP_INV

Regards,
Pointers
Re: same sequence number for repeating data [message #577877 is a reply to message #577876] Thu, 21 February 2013 07:22 Go to previous messageGo to next message
guddu_12
Messages: 175
Registered: April 2012
Location: UK
Senior Member
but for incremental load it will not solve the purpose and have to use sequence
Re: same sequence number for repeating data [message #577879 is a reply to message #577873] Thu, 21 February 2013 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
guddu_12 wrote on Thu, 21 February 2013 13:18
you can order the by column emp_name, emp_inv , they formed the uniq number and once you load with sequence so all the 3 column will have unique data that is why i don't want rank.


I don't understand what you want
You say you want data that are obviously not unique in your first post result and now you say you want unique data.
Then unique or not?

Regards
Michel

Re: same sequence number for repeating data [message #577881 is a reply to message #577867] Thu, 21 February 2013 07:57 Go to previous message
joy_division
Messages: 4544
Registered: February 2005
Location: East Coast USA
Senior Member
guddu_12 wrote on Thu, 21 February 2013 06:58
because the tale can go under update


I have no idea what this means.
Previous Topic: Merge with returning Clause
Next Topic: Need equivalent DECODE for CASE statement
Goto Forum:
  


Current Time: Thu Nov 20 21:41:17 CST 2014

Total time taken to generate the page: 0.10585 seconds