Re: How to count how many times a field value changes ?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: Fri, 17 Apr 1998 21:18:39 GMT
Message-ID: <3537c58a.7308649@www.sigov.si>

On Thu, 16 Apr 1998 11:09:11 -0600, xavier.grosjean_at_thepentagon.com wrote:

>Hello
>
>I'm quite new to sql, plsql and Pro-C. Do not laught, I DID NOT ask for it !
>My problem is:
>How to count how many times one column value changes, in relation
>to one other column value
>Ex: How many times does col2 change of values here :
>col1 col2
>111 221
>111 222 change 1
>111 221 change 2
>111 222 change 3
>112 221 change 4
>112 221
>112 222 change 5
>112 222
>112 222
>113 221 not a change: col1 is not the same !
>113 221
>113 222 change 6
>113 221 change 7
>113 223 change 8
>
>I think it might not be done by one single request, and probably needs
>a pls/sql program, with a cursor. But I thought I would ask, just in case.
>So please, let me know !

It can be done with single SQL. But first, a few clarifications:

1. If I understand your requirements correctly, the record is considered "changed" only if col2 changed while col1 remained unchanged. In that case, what you marked as "change 4" is not realy a change, since col1 changed from 111 to 112 - I belive it was a typo. So, in your example, there are actualy 7 records that meet your "changing" condition.
2. To compared a particular record to its previous record, you need to have a separate column (aka primary key), so that you can allways retrive records in the same order using this column to dictate the order of records.

I put the records from your example into a test table and added a column "id" with distinct values, reflecting the order of records in your example.

SQL> select * from test;

       ID COL1 COL2
--------- --------- ---------

        1       111       221
        2       111       222
        3       111       221
        4       111       222
        5       112       221
        6       112       221
        7       112       222
        8       112       222
        9       112       222
       10       113       221
       11       113       221
       12       113       222
       14       113       223
       13       113       221

14 rows selected.

Here is a SELECT that retrives only the records with changes regarding their previous records:

SQL> SELECT v.id, v.col1, v.col2, ROWNUM change#   2 FROM test t,
  3 (SELECT id, col1, col2 FROM test) v   4 WHERE t.id = v.id - 1
  5 AND t.col1 = v.col1 AND t.col2 != v.col2;

       ID COL1 COL2 CHANGE# --------- --------- --------- ---------

        2       111       222         1
        3       111       221         2
        4       111       222         3
        7       112       222         4
       12       113       222         5
       13       113       221         6
       14       113       223         7

7 rows selected.

This will work with Oracle 7.2 and higher. For 7.0 or 7.1 you have to create view "v" explicitely, not within the FROM caluse.

>Regards and thanks.
>Xavier

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Apr 17 1998 - 16:18:39 CDT