stackoverflow#49905992

View All Public Scripts Login to Run Script

Statement 1

      create table t1 ( c1 varchar2(20), c2 varchar2(20), c3 varchar2(20), c4 varchar2(20))

Table created.

Statement 2

      insert into t1 values ('John','Kennedy','Marc','Guy')

1 row(s) inserted.

Statement 3

      insert into t1 values ('John','Kennedy','Olivier','Oslo')

1 row(s) inserted.

Statement 4

      insert into t1 values ('not','john','vijay','balebail')

1 row(s) inserted.

Statement 6

      select t1.*, count(c1||c2) over (partition by c1,c2 order by c1,c2  ) flag from t1


C1 C2 C3 C4 FLAG
John Kennedy Marc Guy 2
John Kennedy Olivier Oslo 2
not john vijay balebail 1

3 rows selected.

Statement 7

      select t1.*, decode (count(c1||c2) over (partition by c1,c2 order by c1,c2  ),1,0,1) flag from t1


C1 C2 C3 C4 FLAG
John Kennedy Marc Guy 1
John Kennedy Olivier Oslo 1
not john vijay balebail 0

3 rows selected.

Statement 9

      select t1.*, count(c1||c2) over (partition by c1,c2 order by c1,c2  ) flag from t1


C1 C2 C3 C4 FLAG
John Kennedy Marc Guy 2
John Kennedy Olivier Oslo 2
not john vijay balebail 1

3 rows selected.

Statement 10

      select t1.*, decode (count(c1||c2) over (partition by c1,c2 order by c1,c2  ),1,0,1) flag from t1


C1 C2 C3 C4 FLAG
John Kennedy Marc Guy 1
John Kennedy Olivier Oslo 1
not john vijay balebail 0

3 rows selected.

Additional Information