Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
[8i] Is it possible to add a row number that increases based on a column?

696240
Member Posts: 511
I have the following sample table and data:
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?
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 8I 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
-
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.You have some very good instincts!
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...
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
-
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.You have some very good instincts!
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...
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 -
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 -
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 ;
-
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.