[8i] Is it possible to add a row number that increases based on a column?

696240 · Feb 18, 2010 1:57PM

I have the following sample table and data:

CREATE TABLE ords
(	ord_nbr		NUMBER	NOT NULL
,	step_nbr	NUMBER
,	area_code	VARCHAR2(2)
	CONSTRAINT caln_pk PRIMARY KEY (ord_nbr)
);

INSERT INTO	ords
VALUES (1,1,'A1');
INSERT INTO	ords
VALUES (1,2,'B1');
INSERT INTO	ords
VALUES (1,3,'B1');
INSERT INTO	ords
VALUES (1,4,'B2');
INSERT INTO	ords
VALUES (1,5,'A1');
INSERT INTO	ords
VALUES (1,6,'C1');
INSERT INTO	ords
VALUES (1,7,'X1');
INSERT INTO	ords
VALUES (1,8,'C2');
INSERT INTO	ords
VALUES (1,9,'C2');
INSERT INTO	ords
VALUES (1,10,'Z9');

The results I want to get look like:

ORD_NBR	STEP_NBR	AREA_CODE	SEQ_NBR
-----------------------------------------------
1	1		A1		1
1	2		B1		2
1	3		B1		2
1	4		B2		3
1	5		A1		4
1	6		C1		5
1	7		X1		6
1	8		C2		7
1	9		C2		7
1	10		Z9		8

I only want the SEQ_NBR to increase if the area changes... I'm wondering if there's I need to turn this into a hierarchical query somehow, so that there's some relationship between step 1 and step 2, step 2 and step 3, etc.

I've played around with ROW_NUMBER, RANK, and DENSE_RANK, but I can't seem to figure out how to add that SEQ_NBR column...

Any suggestions?

Frank Kulash · Feb 18, 2010 2:18PM

Hi,

This does what you requested:

SELECT	ord_nbr, step_nbr, area_code
,	SUM (new_grp) OVER ( PARTITION BY  ord_nbr
	    	      	     ORDER BY  	   step_nbr
			   ) AS seq_nbr
FROM	(
	    SELECT  ord_nbr, step_nbr, area_code
	    ,	    CASE
			 WHEN  area_code = LAG (area_code) OVER ( PARTITION BY  ord_nbr
			       		       		   	  ORDER BY  	step_nbr
								)
			 THEN  0
			 ELSE  1
	    	    END		AS new_grp
	    FROM    ords
	)
ORDER BY  ord_nbr
,     	  step_nbr
;

I'm guessing at what role ord_nbr plays in this problem.

user11033437 wrote:
I have the following sample table and data:

CREATE TABLE ords
(	ord_nbr		NUMBER	NOT NULL
,	step_nbr	NUMBER
,	area_code	VARCHAR2(2)
	CONSTRAINT caln_pk PRIMARY KEY (ord_nbr)
);

Did part of your PRIMARY KEY constraint get lost? It looks like ord_nbr is not distinct, so it can't be a primary key by itself.

... I'm wondering if there's I need to turn this into a hierarchical query somehow, so that there's some relationship between step 1 and step 2, step 2 and step 3, etc.

I've played around with ROW_NUMBER, RANK, and DENSE_RANK, but I can't seem to figure out how to add that SEQ_NBR column...

You have some very good instincts!
I think you could do this as a CONNECT BY query, but I don't think it would be any simpler or more efficient. It's good to keep CONNECT BY in mind, and recognize that CONNECT BY is a potential solution for this kind of problem.
This sure looks like a DENSE_RANK problem. That's what I trid first, but I couldn't find a solution simpler than the one above.

Thanks for posting the CREATE TABLE and INSERT statements. That's very helpful.
If you're using Oracle 8.1, that's very significant. You should mention your version in the body of the message, and not just hope that someone notices "8i" in the subject line or tags.

Edited by: Frank Kulash on Feb 18, 2010 2:22 PM

Frank Kulash · Feb 18, 2010 2:18PM

Hi,

This does what you requested:

SELECT	ord_nbr, step_nbr, area_code
,	SUM (new_grp) OVER ( PARTITION BY  ord_nbr
	    	      	     ORDER BY  	   step_nbr
			   ) AS seq_nbr
FROM	(
	    SELECT  ord_nbr, step_nbr, area_code
	    ,	    CASE
			 WHEN  area_code = LAG (area_code) OVER ( PARTITION BY  ord_nbr
			       		       		   	  ORDER BY  	step_nbr
								)
			 THEN  0
			 ELSE  1
	    	    END		AS new_grp
	    FROM    ords
	)
ORDER BY  ord_nbr
,     	  step_nbr
;

I'm guessing at what role ord_nbr plays in this problem.

user11033437 wrote:
I have the following sample table and data:

CREATE TABLE ords
(	ord_nbr		NUMBER	NOT NULL
,	step_nbr	NUMBER
,	area_code	VARCHAR2(2)
	CONSTRAINT caln_pk PRIMARY KEY (ord_nbr)
);

Did part of your PRIMARY KEY constraint get lost? It looks like ord_nbr is not distinct, so it can't be a primary key by itself.

... I'm wondering if there's I need to turn this into a hierarchical query somehow, so that there's some relationship between step 1 and step 2, step 2 and step 3, etc.

I've played around with ROW_NUMBER, RANK, and DENSE_RANK, but I can't seem to figure out how to add that SEQ_NBR column...

You have some very good instincts!
I think you could do this as a CONNECT BY query, but I don't think it would be any simpler or more efficient. It's good to keep CONNECT BY in mind, and recognize that CONNECT BY is a potential solution for this kind of problem.
This sure looks like a DENSE_RANK problem. That's what I trid first, but I couldn't find a solution simpler than the one above.

Thanks for posting the CREATE TABLE and INSERT statements. That's very helpful.
If you're using Oracle 8.1, that's very significant. You should mention your version in the body of the message, and not just hope that someone notices "8i" in the subject line or tags.

Edited by: Frank Kulash on Feb 18, 2010 2:22 PM

696240 · Feb 18, 2010 2:21PM

Yes, my primary key was supposed to include both ord_nbr and step_nbr (oops).

Let me give your query a shot... I just looked up the lag function (never used it before), and I think that's exactly what I needed. I'll reply again and award points once I have confirmed this.

Thanks!

Edit: I will be sure to mention the 8i bit in the body in the future... I guess so far I've had good luck that people have always noticed it in my subject.

Edited by: user11033437 on Feb 18, 2010 12:22 PM

Frank Kulash · Feb 18, 2010 2:42PM

Hi,

This CONNECT BY solution is much simpler than I would have expected.
Unfortunately, it uses SYS_CONNECT_BY_PATH, which was introduced in Oracle 9, so it won't help you. I just posted it in case others are interested.

SELECT	ord_nbr, step_nbr, area_code
,	LENGTH ( TRANSLATE ( SYS_CONNECT_BY_PATH ( CASE
							WHEN  area_code = PRIOR area_code
				   			THEN  0
				   			ELSE  1
			      			    END
			    			  , '+'
			    			  )
			   , '10+'
			   , '1'       -- remove all 0s and +s
			   )
		)		AS seq_nbr
FROM	  ords
START WITH	step_nbr	= 1
CONNECT BY	step_nbr	= PRIOR step_nbr + 1
ORDER BY  ord_nbr
,     	  step_nbr
;

696240 · Feb 18, 2010 4:21PM

Thanks, the LAG function worked great for this. I can think of a number of times where, had I known about LAG, it would have been really useful. I saw there's a related function called LEAD that I'll probably also find to be very useful.

As always, thanks very much!

Forum Stats

Quick Links

Categories

[8i] Is it possible to add a row number that increases based on a column?

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

[8i] Is it possible to add a row number that increases based on a column?

Best Answer

Answers