Group by Sequence Numbers?

[phatz]

Hi,

Apologies if this is in the wrong section. I am basically after sql that will do the following. I am after a sequential set of numbers in relation to when column B changes but keeping column ID in sequence
I have tried Dense Rank but it always throws out the sequence of the ID column which messes what I am after.

ID||A||B
01||T||1
02||T||1
03||T||0
04||T||0
05||T||1
06||T||1
07||T||1
08||T||0
09||T||1

INTO

ID||A||B||C
01||T||1||1
02||T||1||1
03||T||0||2
04||T||0||3
05||T||1||4
06||T||1||4
07||T||1||4
08||T||0||5
09||T||1||6

[Tim...]

Hi.

When you post a question like this, you should really post a setup script like this.

Code: Select all

DROP TABLE t1;
CREATE TABLE t1 (
  id NUMBER,
  a  VARCHAR2(1),
  b  NUMBER(1)
);

INSERT INTO t1 VALUES (1, 'T', 1);
INSERT INTO t1 VALUES (2, 'T', 1);
INSERT INTO t1 VALUES (3, 'T', 0);
INSERT INTO t1 VALUES (4, 'T', 0);
INSERT INTO t1 VALUES (5, 'T', 1);
INSERT INTO t1 VALUES (6, 'T', 1);
INSERT INTO t1 VALUES (7, 'T', 1);
INSERT INTO t1 VALUES (8, 'T', 0);
INSERT INTO t1 VALUES (9, 'T', 1);
COMMIT;

If you want people to help, you should really make it as easy as possible for them.

This query works for me.

Code: Select all

SELECT -- Apply the change RANK to all records within the same group
       id, a, b,
       CASE
         WHEN change = 'SAME' THEN (LAG(change_rank, 1, NULL) OVER (ORDER BY id))
         ELSE change_rank
       END AS c
FROM  (SELECT id, a,b,change,
               -- Assign a RANK to the CHANGE records
               RANK() OVER (PARTITION BY change ORDER BY id) change_rank
        FROM   (SELECT id, a, b,
                       -- Idenitfy when B changes using LAG.
                       CASE
                         WHEN (LAG(b, 1, NULL) OVER (ORDER BY id)) IS NULL THEN 'CHANGE'
                         WHEN (LAG(b, 1, NULL) OVER (ORDER BY id)) <> b THEN 'CHANGE'
                         ELSE 'SAME'
                       END AS change
                FROM   t1
                ORDER BY id)
        ORDER BY id);
       
ID A B C
-- - - -
 1 T 1 1
 2 T 1 1
 3 T 0 2
 4 T 0 2
 5 T 1 3
 6 T 1 3
 7 T 1 3
 8 T 0 4
 9 T 1 5

 9 rows selected

If you work from the inside out...

1) Use LAG to identify when the value of B has changed.
2) Take those results and use RANK to give a rank to the records marked as CHANGE.
3) Take those results and use LAG again to keep the previous change_rank if the record is marked as SAME.

That saves you having to use PL/SQL to do it.

Cheers

Tim...