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.
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 (
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);
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,
WHEN change = 'SAME' THEN (LAG(change_rank, 1, NULL) OVER (ORDER BY id))
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.
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'
END AS change
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.
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 5 guests