Oracle- sql query to print odd number of rows when we do not have number data type columns

Question

I was trying to print odd numbers of rows from my table without taking taking help of my numeric cloumns

when I try to execute this query I was getting only first row.

select * from emp3 where mod(rownum,2)=1;

emp3 is my table name. and when I use my one of the numeric columns in place of rownum I was getting desired output.

select * from emp3 where mod(eid,2)=1 order by eid;

where eid is a numeric column in the table. But what if do not have a numeric column and I want to print only odd number of rows from the table? Help me!

Hm, this doesn't make much sense. In a relational database, think of rows as balls in the basked. Which ones of them are "odd"? You have to have *something* which will tell you that information. In your "basket" (table), it is the EID column. So, why wouldn't you use it? — Littlefoot, Mar 09 '20 at 06:10
@Littlefoot sometimes eid may be mix of numbers and alaphabets, then what I have to do? — Rohith, Mar 09 '20 at 06:12
Let me rephrase @Littlefoot's question. Suppose ALL your id's were alphabetic, not numeric. There are no other columns with numbers in them. What do you mean by the "odd" (and the "even") rows in the table? Rows in a table don't come with an ordinal number: "this is the first row, this is the second; this is the third, and this is the fourth". The question to you is, how do you define "odd" rows? — , Mar 09 '20 at 06:16
I posted a couple of examples what you might do in that case. Have a look,. please. — Littlefoot, Mar 09 '20 at 06:16

score 1 · Answer 1 · answered Mar 09 '20 at 06:16

If the EID column isn't numeric, then use something that is. For example, ROW_NUMBER gives such an information:

SQL> with temp as
  2    (select empno, ename, job sal,
  3       row_number() over (order by null) rn
  4     from emp
  5    )
  6  select *
  7  from temp
  8  where mod(rn, 2) = 1;

     EMPNO ENAME      SAL               RN
---------- ---------- --------- ----------
      7369 SMITH      CLERK              1
      7521 WARD       SALESMAN           3
      7654 MARTIN     SALESMAN           5
      7782 CLARK      MANAGER            7
      7839 KING       PRESIDENT          9
      7876 ADAMS      CLERK             11
      7902 FORD       ANALYST           13

7 rows selected.

SQL>

Or even ROWNUM you already tried to use:

SQL> with temp as
  2    (select empno, ename, job sal,
  3       rownum rn
  4     from emp
  5    )
  6  select *
  7  from temp
  8  where mod(rn, 2) = 1;

     EMPNO ENAME      SAL               RN
---------- ---------- --------- ----------
      7369 SMITH      CLERK              1
      7521 WARD       SALESMAN           3
      7654 MARTIN     SALESMAN           5
      7782 CLARK      MANAGER            7
      7839 KING       PRESIDENT          9
      7876 ADAMS      CLERK             11
      7902 FORD       ANALYST           13

7 rows selected.

SQL>

I liked your comment (about "odd rows" not making any sense) much better than this answer! :-) — , Mar 09 '20 at 06:17

score 1 · Accepted Answer · edited Mar 09 '20 at 06:49

1

Try to execute the below query

select * from (select rownum rn ,column from column_name) where mod(rn,2) <> 0

and please refer to this link for better understanding the concept of rownum https://www.youtube.com/watch?v=QMyw1jumGyQ

edited Mar 09 '20 at 06:49

Rohith

135
1
10

answered Mar 09 '20 at 06:31

abvr1018

26
3

Oracle- sql query to print odd number of rows when we do not have number data type columns

2 Answers2