| how to give sequence number to my table group by id? [message #21035] |
Mon, 08 July 2002 08:19  |
Jenny
Messages: 7
Registered: November 2001
|
Junior Member |
|
|
I have a table "DEPT"(id, location, subid)
I want to insert some data such as:
1, abc, New York, 1
1, sfg, New York, 2
1, xyz, New York, 3
2, fff, San Hose, 1
2, ttt, San Hose, 2
I need to generate a sequence number for "subid" for the same id group. Can anybody help to give me some hints?
Thanks!
-jenny
|
|
|
|
| Re: how to give sequence number to my table group by id? [message #21037 is a reply to message #21035] |
Mon, 08 July 2002 09:29  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
your example (and the name SUBID) suggest that this is a foreign key to a parent table. If so, then you cant just "generate" a sequence number for it. You need to identify the row in the parent table which you want to categorize that row in dept with and then take it's ID and use that value when you intert into dept.
|
|
|
|
|
|
| Re: how to give sequence number to my table group by id? [message #21078 is a reply to message #21035] |
Wed, 10 July 2002 19:34 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Not sure what exactly you want. You are saying a subid and again you mean a sequence number for the same id group. I guess what you want is to give a sequence number that starts newly for new group, just like youhave mentioned in your example, 1,2,3 for New York, and 1,2 for San Jose.
If it is and I am right, you can use rank over().
Try this out.
SELECT ID, NAME, LOCATION, RANK() OVER(PARTITION BY LOCATION ORDER BY NAME) SUBID FROM your_table;
Please see your example lists 4 items in a row but your insert statement shows only three items. I assume it is by mistake and give example field name NAME for second item in the list.
Note: Since RANK OVER() is being used in a SELECT statement, INSERT can be given in conjuction with a SELECT to retrieve the date from another along with this ranking logic. Check it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
