Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Generate common sequence for a group of records

    I'm trying to create a common sequence for a group of records by calling a database sequence. Sequence need to be generated based on DEPT_CD column.

    Input
    Code:
    DEPT_CD,STATUS,START_DT
    100,Active    ,Jan 10 2013
    100,Active    ,Jan 10 2013
    100,Active    ,Jan 10 2013
    100,Active    ,Jan 10 2013
    Expected Output
    Code:
    DEPT_CD,STATUS,START_DT, SEQ
    100,Active    ,Jan 10 2013 , 1
    100,Active    ,Jan 10 2013, 1
    100,Active    ,Jan 10 2013, 1
    100,Active    ,Jan 10 2013, 1
    So far what i have tried is

    Code:
    WITH
             SOURCE AS (
                             SELECT DEPT_CD,STATUS,START_DT FROM TABLE1
                             ),
    GENSEQ AS          (
                             SELECT DEPT_CD,SCHEMA.TESTSEQ.NEXTVAL AS SEQ
                             FROM
                             (SELECT DEPT_CD,ROW_NUMBER() OVER(PARTITION BY                      DEPT_CD ORDER BY START_DT) as DUP FROM SOURCE) A
                             WHERE A.DUP=1
                             )
    SELECT DEPT_CD,STATUS,START_DT,SEQ FROM SOURCE B LEFT JOIN GENSEQ C ON B.DEPT_CD=C.DEPT_CD
    But this query returns unique Sequence no for each number. Can anyone throw some light on this?.

    This query output

    Code:
    DEPT_CD,STATUS,START_DT, SEQ
    100,Active    ,Jan 10 2013 , 1
    100,Active    ,Jan 10 2013, 2
    100,Active    ,Jan 10 2013, 3
    100,Active    ,Jan 10 2013, 4

    Thanks,
    Laknar

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Code:
    select t.*, dense_rank() over(order by DEPT_CD) seq
    from table(values 
      (100, 'A')
    , (100, 'B')
    , (200, 'C')
    , (200, 'D')
    ) t (DEPT_CD, STATUS)
    
    DEPT_CD     STATUS SEQ                 
    ----------- ------ --------------------
            100 A                         1
            100 B                         1
            200 C                         2
            200 D                         2
    Regards,
    Mark.

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Try:

    RANK() OVER(PARTITION BY DEPT_CD ORDER BY START_DT)
    --
    Lennart

  4. #4
    Join Date
    Jul 2008
    Posts
    94
    But i can use either RANK or DENSE_RANK. but i have to use database sequence to generate.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by laknar View Post
    but i have to use database sequence to generate.
    Can you be more specific on this?
    What is so strange requirement - to use necessarily a database sequence to achieve the goal?
    Regards,
    Mark.

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    I agree with Mark, it is difficult to guess what it is you are trying to do. If the purpose of the sequence is to be an offset for the rank something like this might do:

    Code:
    with t (x) as ( values 1,1,2,2,3 )
           , seq (n) as ( values nextval for lelle.SAMPSEQUENCE ) 
    select t.x, seq.n, dense_rank() over (order by t.x) + seq.n 
    from t, seq
    
    X           N                    3                   
    ----------- -------------------- --------------------
              1                    9                   10
              1                    9                   10
              2                    9                   11
              2                    9                   11
              3                    9                   12
    
      5 record(s) selected.
    --
    Lennart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •