This forum is currently locked. You can't register or post questions at this time. (read more)

Group by Sequence Numbers?

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

phatz
Member
Posts: 1
Joined: Wed Jul 25, 2012 11:52 pm

Group by Sequence Numbers?

Postby phatz » Thu Jul 26, 2012 12:01 am

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...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Group by Sequence Numbers?

Postby Tim... » Fri Jul 27, 2012 8:05 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests

cron