How One can easily select all even, odd, or Nth rows from a table using SQL que

How One can easily select all even, odd, or Nth rows from a
table using SQL queries?

Question Posted / guru

4 Answers
20140 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How One can easily select all even, odd, or Nth rows from a table using SQL queries?..

Answer / guru

Please Use Below Query and Kindly post reply feedback

Odd number of records:

select * from emp where (rowid,1) in (select rowid,
mod(rownum,2) from emp);
Output:-
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid,
mod(rownum,2) from emp)
Output:-
2
4
6

For nth number,
Example we r considering number n=10
select * from emp where (rowid,0) in (select rowid,
mod(rownum,10) from emp)

Is This Answer Correct ?

18 Yes

8 No

How One can easily select all even, odd, or Nth rows from a table using SQL queries?..

Answer / mritunjay

hi..mr guru all are correct except for the Ntn row
Odd number of records:
select * from emp where (rowid,1) in (select rowid,
mod(rownum,2) from emp);
Output:-
1
3
5
Even number of records:
select * from emp where (rowid,0) in (select rowid,
mod(rownum,2) from emp)
Output:-
2
4
6
For nth number,
Example we r considering number n=10
select * from emp where (rowid,1) in (select rowid,
rownum/:N) from emp)

Is This Answer Correct ?

12 Yes

8 No

How One can easily select all even, odd, or Nth rows from a table using SQL queries?..

Answer / mahesh

SIMPLE ODD AND EVEN...

SQL> SELECT ROWNUM FROM EMP GROUP BY ROWNUM HAVING MOD(ROWNUM,2)=1
2 /

ROWNUM
----------
1
3
5

1* SELECT ROWNUM FROM EMP GROUP BY ROWNUM HAVING MOD(ROWNUM,2)=0
2 /

ROWNUM
----------
2
4
Goahead...

Is This Answer Correct ?

0 Yes

0 No

How One can easily select all even, odd, or Nth rows from a table using SQL queries?..

Answer / guru

SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);

Is This Answer Correct ?

6 Yes

17 No

Post New Answer