Solved

Increment Sequence Number through SQL

Posted on 2011-02-15
2
910 Views
Last Modified: 2012-05-11
Hi,

I have a SQL query which will fetch the data in below format. Seq_NUM column is a dynamic column.I have to increment the seq number from one for every new FIELD1 value like below.

Is it possible to do it through SQL?
FIELD1	FIELD2	FIELD3	SEQ_NUM
-----------------------------------------------------------
AAA	name1	phone	1
AAA	name2	phone	2
AAA	name3	phone	3
AAA	name4	phone	4
BBB	name1	phone	1
BBB	name2	phone	2
CCC	name1	phone	1
CCC	name2	phone	2
CCC	name1	phone	3
CCC	name2	phone	4
CCC	name1	phone	5
CCC	name2	phone	6

Open in new window

0
Comment
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34895879
this should do:
select FIELD1  , FIELD2  , FIELD3
  ,ROW_NUMBER() OVER (PARTITION BY FIELD1 ORDER BY field2,field3) SEQ_NUM
 from yourtable

Open in new window

0
 

Author Closing Comment

by:Suriyaraj_Sudalaiappan
ID: 34895912
GOOD ONE
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now