Forum Stats

  • 3,836,793 Users
  • 2,262,193 Discussions
  • 7,900,114 Comments

Discussions

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

696240
696240 Member Posts: 511
edited Feb 18, 2010 4:47PM in SQL & PL/SQL
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?

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,208 Red Diamond
    edited Feb 18, 2010 2:26PM Answer ✓
    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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,208 Red Diamond
    edited Feb 18, 2010 2:26PM Answer ✓
    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
    696240 Member Posts: 511
    edited Feb 18, 2010 2:23PM
    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
    Frank Kulash Member, Moderator Posts: 42,208 Red Diamond
    edited Feb 18, 2010 4:47PM
    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
    696240 Member Posts: 511
    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!
This discussion has been closed.