OraFAQ Forum: SQL & PL/SQL » SQL to generate sequential numbers for each repeating row and reset upon new key

Home » SQL & PL/SQL » SQL & PL/SQL » SQL to generate sequential numbers for each repeating row and reset upon new key (Oracle 9i R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL to generate sequential numbers for each repeating row and reset upon new key [message #603162]

Wed, 11 December 2013 22:53

yasar2002
Messages: 4
Registered: November 2009
Location: Dubai

Junior Member

Hi all,

I know how to increment a number in a column using PL/SQL for every row that repeats. In the example below, ColB contains such data which needs to be displayed as autoincrement as 1 but to reset once value in colA changes.

I have data in a table that has repititive rows such as:

         ColA ColB ColC
Row1: 8     1      x
Row2: 8     1      x
Row3: 8     1      x
Row4: 9     1      x
Row5: 9     1      x

The desired format is:

         ColA ColB ColC
Row1: 8     1      x
Row2: 8     2      x
Row3: 8     1      x
Row4: 9     2      x
Row5: 9     3      x

Please advise if it is doable in SQL directly.

Thanks,

YM

[EDITED by LF: applied [pre] tags to preserve formatting]

[Updated on: Sat, 14 December 2013 09:40] by Moderator

Report message to a moderator

Re: SQL to generate sequential numbers for each repeating row and reset upon new key [message #603168 is a reply to message #603162]

Wed, 11 December 2013 23:53

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Can you recheck you desired output. It should be :

Row1: 8 1 x
Row2: 8 2 x
Row3: 8 3 x
Row4: 9 1 x
Row5: 9 2 x

If above is incorrect then please mention the rules to get the output.

My immediate answer would be to use analytic function :

ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL1) rn

Now my question is, do you want the output just as a display or you want to update the table in this format?

SQL> WITH DATA AS
  2   (SELECT 8 A, 1 B, 'x' C
  3      FROM DUAL
  4    UNION ALL
  5    SELECT 8, 1, 'x'
  6      FROM DUAL
  7    UNION ALL
  8    SELECT 8, 1, 'x'
  9      FROM DUAL
 10    UNION ALL
 11    SELECT 9, 1, 'x'
 12      FROM DUAL
 13    UNION ALL
 14    SELECT 9, 1, 'x' FROM DUAL)
 15  SELECT A COL1, RN COL2, C COL3
 16    FROM(
 17  SELECT A, B, C, ROW_NUMBER() OVER(PARTITION BY A
 18   ORDER BY A) RN
 19    FROM DATA);
 
      COL1       COL2 COL3
---------- ---------- ----
         8          1 x
         8          2 x
         8          3 x
         9          1 x
         9          2 x

[Updated on: Sat, 08 March 2014 02:05] by Moderator

Report message to a moderator

Re: SQL to generate sequential numbers for each repeating row and reset upon new key [message #603213 is a reply to message #603162]

Thu, 12 December 2013 03:22

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> break on deptno skip 1 dup
SQL> select ename, sal, deptno, 
  2         row_number() over (partition by deptno  order by sal) rn
  3  from emp
  4  order by deptno, rn
  5  /
ENAME             SAL     DEPTNO         RN
---------- ---------- ---------- ----------
MILLER           1300         10          1
CLARK            2450         10          2
KING             5000         10          3

SMITH             800         20          1
ADAMS            1100         20          2
JONES            2975         20          3
SCOTT            3000         20          4
FORD             3000         20          5

JAMES             950         30          1
MARTIN           1250         30          2
WARD             1250         30          3
TURNER           1500         30          4
ALLEN            1600         30          5
BLAKE            2850         30          6

Report message to a moderator

Re: SQL to generate sequential numbers for each repeating row and reset upon new key [message #605861 is a reply to message #603168]

Wed, 15 January 2014 07:02

yasar2002
Messages: 4
Registered: November 2009
Location: Dubai

Junior Member

Hi All,

I used the following query to generate line numbers in the desired output.

The repeating column is jpnum. To list all attached documents in each record and to have a unique serial assigned for each relevant document, this worked:

select jpnum , row_number() over (partition by jpnum order by jpnum) Sequence,
'Item Internal' CATEGORY, docinfo.document title, docinfo.description, URLTYPE datatype, substr(urlname, instr(urlname,'\', -1)+1) urlname
FROM docinfo, doclinks, jobplan
WHERE doclinks.docinfoid = docinfo.docinfoid
AND ownertable = 'JOBPLAN'
AND jobplan.jobplanid = ownerid;

Report message to a moderator

Re: SQL to generate sequential numbers for each repeating row and reset upon new key [message #605868 is a reply to message #605861]

Wed, 15 January 2014 08:01

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

row_number() over (partition by jpnum order by jpnum)

This is surely wrong somewhere.
If you partition by jpnum then all rows in the partition have the same jpnum and so "order by jpnum" is meaningless.
See my query.

[Updated on: Sat, 08 March 2014 02:05]

Report message to a moderator

Previous Topic:	Want to use date variable value in select query
Next Topic:	why would this query return three rows?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 18:41:54 CDT 2024