Home » SQL & PL/SQL » SQL & PL/SQL » Repeated Sequence generation in Table
Repeated Sequence generation in Table [message #196978] Mon, 09 October 2006 07:36 Go to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Hi,

How do we generate a repeatable sequence based on some values in the table? For example, I have a table like this -

Deptt City State Country Manager <more columns>
Finance Delhi DEL IND Ram ...
Finance Delhi DEL IND Avinash ...
Sales Mumbai MAH IND Vikaram ...
Sales Mumbai MAH IND Raj ...
Sales Mumbai MAH IND Kavita ...

And I want a display like this -

Deptt City State Country Number
Finance Delhi DEL IND 1
Finance Delhi DEL IND 2
Sales Mumbai MAH IND 1
Sales Mumbai MAH IND 2
Sales Mumbai MAH IND 3

I would like to do it only by using SELECT, no PL/SQL. Is that possible?

Thanks and regards,
Attraxion.
Re: Repeated Sequence generation in Table [message #196979 is a reply to message #196978] Mon, 09 October 2006 07:38 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Use a windowing function like RANK() or ROW_NUMBER.
Re: Repeated Sequence generation in Table [message #196983 is a reply to message #196979] Mon, 09 October 2006 08:05 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi


U can use the decode function of Oracle ...

bye
ashu
Re: Repeated Sequence generation in Table [message #196985 is a reply to message #196978] Mon, 09 October 2006 08:10 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Can you elaborate on that solution. There are different names appearing against each department, so you can't simply decode the name to a specific number. So how do you envisage doing it ?
Re: Repeated Sequence generation in Table [message #196993 is a reply to message #196985] Mon, 09 October 2006 08:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm with Cthulhu on this one.

the script:
create table mhe_foo( Deptt varchar2(10)
                    , City  varchar2(10)
                    , State varchar2(3)
                    , Country varchar2(3)
                    )
/

INSERT INTO mhe_foo VALUES ('Finance', 'Delhi' , 'DEL', 'IND');
INSERT INTO mhe_foo VALUES ('Finance', 'Delhi' , 'DEL', 'IND');
INSERT INTO mhe_foo VALUES ('Sales'  , 'Mumbai', 'MAH', 'IND');
INSERT INTO mhe_foo VALUES ('Sales'  , 'Mumbai', 'MAH', 'IND');
INSERT INTO mhe_foo VALUES ('Sales'  , 'Mumbai', 'MAH', 'IND');

SELECT deptt
     , city
     , state
     , country
     , row_number() over ( partition by deptt order by deptt ) thenumber
FROM   mhe_foo
/

DROP TABLE mhe_foo
/


The run:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


DEPTT      CITY       STA COU  THENUMBER
---------- ---------- --- --- ----------
Finance    Delhi      DEL IND          1
Finance    Delhi      DEL IND          2
Sales      Mumbai     MAH IND          1
Sales      Mumbai     MAH IND          2
Sales      Mumbai     MAH IND          3


Table dropped.

SQL>


MHE
Re: Repeated Sequence generation in Table [message #196994 is a reply to message #196978] Mon, 09 October 2006 08:32 Go to previous messageGo to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Hi,

Thanks for you idea, Cthulhu. I used the dense_rank() function in the following way:

SELECT DEPTT, CITY, COUNTRY,
DENSE_RANK() OVER (ORDER BY MANAGER) AS NUMBER
FROM TABLE_NAME
ORDER BY DEPTT, CITY, COUNTRY, NUMBER;

And I am getting this:

Deptt City Country Number
FINANCE DEL IND 1
FINANCE DEL IND 2
FINANCE DEL IND 9
FINANCE DEL IND 11
FINANCE DEL IND 13
FINANCE DEL IND 14
FINANCE DEL IND 15
FINANCE DEL IND 23
FINANCE DEL IND 25
FINANCE DEL IND 27
FINANCE DEL IND 30
FINANCE DEL IND 32

Any pointers on why I am not getting consecutive numbers?

Thanks and regards,
Att.
Re: Repeated Sequence generation in Table [message #196995 is a reply to message #196978] Mon, 09 October 2006 08:34 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Need to partition by the dept e.g.

SELECT DEPTT, CITY, COUNTRY,
DENSE_RANK() OVER (PARTITION BY DEPTT, CITY, COUNTRY ORDER BY MANAGER) AS NUMBER
FROM TABLE_NAME
ORDER BY DEPTT, CITY, COUNTRY, NUMBER;

[Updated on: Mon, 09 October 2006 08:34]

Report message to a moderator

Re: Repeated Sequence generation in Table [message #196996 is a reply to message #196978] Mon, 09 October 2006 08:34 Go to previous messageGo to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Maaher, I just posted my message without seeing yours. Okay I'll try the row number function. Thanks.
Re: Repeated Sequence generation in Table [message #196998 is a reply to message #196978] Mon, 09 October 2006 08:39 Go to previous messageGo to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Yup, Cthulhu's final reply worked. Thanks guys Smile

Best,
Attraxion.
Re: Repeated Sequence generation in Table [message #197004 is a reply to message #196995] Mon, 09 October 2006 08:49 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Cthulhu wrote on Mon, 09 October 2006 15:34

Need to partition by the dept e.g.
...
Great minds Wink

MHE
Previous Topic: Diff between 2 sql's
Next Topic: Total call Date report using increment date
Goto Forum:
  


Current Time: Tue Dec 06 10:25:39 CST 2016

Total time taken to generate the page: 0.12962 seconds