Forum Stats

  • 3,768,187 Users
  • 2,252,756 Discussions
  • 7,874,485 Comments

Discussions

Version Key creation

591131
591131 Member Posts: 42
edited Nov 6, 2010 5:11AM in SQL & PL/SQL
Hi,

i have a table like

INSERT INTO my_table(
SELECT 9591 L_ID, 'GULSU1' NAME, 111 C_ID, to_date('01-NOV-99') VALID_START_DATE FROM DUAL UNION ALL
SELECT 17107, 'GULSU1', 111, to_date('18-NOV-99') FROM DUAL UNION ALL
SELECT 164522, 'GULSU1', 111, to_date('21-MAR-00') FROM DUAL UNION ALL
SELECT 177619, 'GULSU1', 111, to_date('23-APR-00') FROM DUAL UNION ALL
SELECT 197247, 'GULSU1', 111, to_date('31-MAY-01') FROM DUAL UNION ALL
SELECT 204492, 'GULSU1', 111, to_date('04-JUN-01') FROM DUAL UNION ALL
SELECT 211649, 'GULSU1', 111, to_date('12-JUN-01') FROM DUAL UNION ALL
SELECT 224614, 'GULSU1', 111, to_date('30-APR-03') FROM DUAL UNION ALL
SELECT 363415, 'GULSU1', 111, to_date('24-SEP-04') FROM DUAL UNION ALL
SELECT 9592, 'GULSU2', 112, to_date('01-NOV-99') FROM DUAL UNION ALL
SELECT 17108, 'GULSU2', 112, to_date('18-NOV-99') FROM DUAL UNION ALL
SELECT 164523, 'GULSU2', 112, to_date('21-MAR-00') FROM DUAL UNION ALL
SELECT 177620, 'GULSU2', 112, to_date('23-APR-00') FROM DUAL UNION ALL
SELECT 197248, 'GULSU2', 112, to_date('31-MAY-01') FROM DUAL UNION ALL
SELECT 204493, 'GULSU2', 112, to_date('04-JUN-01') FROM DUAL UNION ALL
SELECT 211650, 'GULSU2', 112, to_date('12-JUN-01') FROM DUAL UNION ALL
SELECT 224615, 'GULSU1', 112, to_date('30-APR-03') FROM DUAL UNION ALL
SELECT 287725, 'GULSU1', 112, to_date('25-MAR-04') FROM DUAL
);

And i select this data with keys like;

QUERY:

SELECT
dense_rank() over (order by C_ID) SK,
row_number() over (partition by C_ID order by VALID_START_DATE) VK,
L_ID,
NAME,
C_ID,
VALID_START_DATE
FROM my_table ORDER BY C_ID,VALID_START_DATE;

The output of this query is:

1 | 1 | 9591 | GULSU1 | 111 | 01-NOV-99
1 | 2 | 17107 | GULSU1 | 111 | 18-NOV-99
1 | 3 | 164522 | GULSU1 | 111 | 21-MAR-00
1 | 4 | 177619 | GULSU1 | 111 | 23-APR-00
1 | 5 | 197247 | GULSU1 | 111 | 31-MAY-01
1 | 6 | 204492 | GULSU1 | 111 | 04-JUN-01
1 | 7 | 211649 | GULSU1 | 111 | 12-JUN-01
1 | 8 | 224614 | GULSU1 | 111 | 30-APR-03
1 | 9 | 363415 | GULSU1 | 111 | 24-SEP-04
2 | 1 | 9592 | GULSU2 | 112 | 01-NOV-99
2 | 2 | 17108 | GULSU2 | 112 | 18-NOV-99
2 | 3 | 164523 | GULSU2 | 112 | 21-MAR-00
2 | 4 | 177620 | GULSU2 | 112 | 23-APR-00
2 | 5 | 197248 | GULSU2 | 112 | 31-MAY-01
2 | 6 | 204493 | GULSU2 | 112 | 04-JUN-01
2 | 7 | 211650 | GULSU2 | 112 | 12-JUN-01
2 | 8 | 224615 | GULSU1 | 112 | 30-APR-03
2 | 9 | 287725 | GULSU1 | 112 | 25-MAR-04

I want the result with one more version key that shows the NAME changes. And I want the output like:

1 | 1 | 1 | 9591 | GULSU1 | 111 | 01-NOV-99
1 | 2 | 1 | 17107 | GULSU1 | 111 | 18-NOV-99
1 | 3 | 1 | 164522 | GULSU1 | 111 | 21-MAR-00
1 | 4 | 1 | 177619 | GULSU1 | 111 | 23-APR-00
1 | 5 | 1 | 197247 | GULSU1 | 111 | 31-MAY-01
1 | 6 | 1 | 204492 | GULSU1 | 111 | 04-JUN-01
1 | 7 | 1 | 211649 | GULSU1 | 111 | 12-JUN-01
1 | 8 | 1 | 224614 | GULSU1 | 111 | 30-APR-03
1 | 9 | 1 | 363415 | GULSU1 | 111 | 24-SEP-04
2 | 1 | 1 | 9592 | GULSU2 | 112 | 01-NOV-99
2 | 2 | 1 | 17108 | GULSU2 | 112 | 18-NOV-99
2 | 3 | 1 | 164523 | GULSU2 | 112 | 21-MAR-00
2 | 4 | 1 | 177620 | GULSU2 | 112 | 23-APR-00
2 | 5 | 1 | 197248 | GULSU2 | 112 | 31-MAY-01
2 | 6 | 1 | 204493 | GULSU2 | 112 | 04-JUN-01
2 | 7 | 1 | 211650 | GULSU2 | 112 | 12-JUN-01
2 | 8 | 2 | 224615 | GULSU1 | 112 | 30-APR-03
2 | 9 | 2 | 287725 | GULSU1 | 112 | 25-MAR-04


How may i do this with a single select statement???

Thanks, BR...

Comments

  • 450441
    450441 Member Posts: 2,525
    Isn't DENSE_RANK() OVER (partition by c_id, name ... ) what you want?
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 6, 2010 5:11AM
    with WorkView as (
    SELECT 'GULSU1' NAME, 111 C_ID,10 as SortKey FROM DUAL UNION
    SELECT 'GULSU1', 111,20 FROM DUAL UNION
    SELECT 'GULSU1', 111,30 FROM DUAL UNION
    SELECT 'GULSU1', 111,40 FROM DUAL UNION
    SELECT 'GULSU1', 111,50 FROM DUAL UNION
    SELECT 'GULSU1', 111,60 FROM DUAL UNION
    SELECT 'GULSU1', 111,70 FROM DUAL UNION
    SELECT 'GULSU1', 111,80 FROM DUAL UNION
    SELECT 'GULSU1', 111,90 FROM DUAL UNION
    SELECT 'GULSU2', 112,100 FROM DUAL UNION
    SELECT 'GULSU2', 112,110 FROM DUAL UNION
    SELECT 'GULSU2', 112,120 FROM DUAL UNION
    SELECT 'GULSU2', 112,130 FROM DUAL UNION
    SELECT 'GULSU2', 112,140 FROM DUAL UNION
    SELECT 'GULSU2', 112,150 FROM DUAL UNION
    SELECT 'GULSU2', 112,160 FROM DUAL UNION
    SELECT 'GULSU1', 112,170 FROM DUAL UNION
    SELECT 'GULSU1', 112,180 FROM DUAL)
    select NAME,C_ID,SortKey,
    sum(willSum) over(partition by NAME order by SortKey) as version
    from (select NAME,C_ID,SortKey,
           case when lnnvl(NAME = Lag(NAME) over(order by SortKey))
                then 1 else 0 end as willSum
           from WorkView)
    order by SortKey;
    
    NAME    C_ID  SortKey  version
    ------  ----  -------  -------
    GULSU1   111       10        1
    GULSU1   111       20        1
    GULSU1   111       30        1
    GULSU1   111       40        1
    GULSU1   111       50        1
    GULSU1   111       60        1
    GULSU1   111       70        1
    GULSU1   111       80        1
    GULSU1   111       90        1
    GULSU2   112      100        1
    GULSU2   112      110        1
    GULSU2   112      120        1
    GULSU2   112      130        1
    GULSU2   112      140        1
    GULSU2   112      150        1
    GULSU2   112      160        1
    GULSU1   112      170        2
    GULSU1   112      180        2
    On Oracke9i,
    we must emulate lnnvl.

    similar thread
    450745

    and my site :-)
    http://www.geocities.jp/oraclesqlpuzzle/10-130.html
This discussion has been closed.