Skip to Main Content
  • Questions

  • Toggle column value for alternate set of rows

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Subbu.

Asked: May 18, 2017 - 5:52 pm UTC

Last updated: May 19, 2017 - 10:37 pm UTC

Version: 11i

Viewed 1000+ times

You Asked

Tom & team,

I want to toggle the value in a column to 0 or 1 for alternate set of rows, can you please let me know the optimal way to accomplish this?

select empno,dept.deptno, 0 counter
from emp, dept
where emp.deptno = dept.deptno
order by dept.deptno;

The output should be like below...
EMPNO DEPTNO COUNTER
7782 10 0
7934 10 0
7839 10 0
7902 20 1
7788 20 1
7566 20 1
7369 20 1
7876 20 1
7521 30 0
7654 30 0
7844 30 0
7900 30 0
7499 30 0
7698 30 0

Thanks,
Subbu

and Connor said...

Something like this ?

SQL> select x.*, mod(rownum,2)
  2  from (
  3  select empno,dept.deptno
  4  from scott.emp, scott.dept
  5  where emp.deptno = dept.deptno
  6  order by dept.deptno
  7  ) x;

     EMPNO     DEPTNO MOD(ROWNUM,2)
---------- ---------- -------------
      7782         10             1
      7839         10             0
      7934         10             1
      7566         20             0
      7902         20             1
      7876         20             0
      7369         20             1
      7788         20             0
      7521         30             1
      7844         30             0
      7499         30             1
      7900         30             0
      7698         30             1


If you need to flip the 1 and 0, then just do:

mod(rownum+1,2)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the answer, but I am looking for something else

Subbu Dhulipala, May 19, 2017 - 10:16 pm UTC

Hi Connor,

Sorry for not explaining you my requirement clearly. I want to flip 0 or 1 for each department(group). The output should be

EMPNO DEPTNO MOD(ROWNUM,2)
---------- ---------- -------------
7782 10 0
7839 10 0
7934 10 0
7566 20 1
7902 20 1
7876 20 1
7369 20 1
7788 20 1
7521 30 0
7844 30 0
7499 30 0
7900 30 0
7698 30 0



Connor McDonald
May 19, 2017 - 10:37 pm UTC 

SQL> select s.*,
  2    dense_rank() over (  order by deptno ) as seq
  3  from scott.emp s;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SEQ
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          1
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          1
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          1
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          2
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20          2
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20          2
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20          2
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20          2
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30          3
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          3
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30          3
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          3
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          3


and apply the same "mod" around it

Got it

Subbu Dhulipala, May 19, 2017 - 10:47 pm UTC

Hi Connor,

I tweaked your query and was able to get the expecting result.

select x.empno,x.deptno, mod(rnum,2)
from (
select empno,dept.deptno,
dense_rank() over(partition by grouping_id(dept.deptno) order by dept.deptno) rnum
from scott.emp, scott.dept
where emp.deptno = dept.deptno
group by empno,dept.deptno
order by dept.deptno) x

Output....
EMPNO DEPTNO MOD(RNUM,2)
----- ------ -----------
7934 10 1
7839 10 1
7782 10 1
7369 20 0
7876 20 0
7902 20 0
7788 20 0
7566 20 0
7499 30 1
7654 30 1
7521 30 1
7698 30 1
7844 30 1
7900 30 1

Can you please let me know if this is optimal way of getting the result?

Thanks,
Subbu


Thanks so much

Subbu Dhulipala, May 19, 2017 - 10:51 pm UTC

Hi Connor - Please disregard my earlier post. Did not see that you have already replied.

Your solution is what I am looking for. Thanks again.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.