how to select even records from a table in oracle?

10
votes

i'm using below query to retrieve even numbered records.but in reslut it is displaying no rows

select ename,job from emp where mod(rownum,2)=0;

why mod(rownum,2) is not working in where conditiom

can u please also give the query to select the odd number records

Is there anything wrong with that query?

suggetions please..

11
sqloracle

11 Answers

16
votes

It is not working because: for the first row ROWNUM is 1 and, in this case, MOD(ROWNUM,2) is 1 and since your WHERE statement is MOD(ROWNUM,2)=0 then this reduces to 1=0 and the row is discarded. The subsequent row will then be tested against a ROWNUM of 1 (since the previous row is no longer in the output and will not have a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail the WHERE test and are discarded.

If you try to get the odd rows this way using WHERE MOD(ROWNUM,2)=1 then it will return the first row only and the second, and subsequent, rows will fail the test and will never be included in the query.

As Vijaykumar Hadalgi suggests, you need to select the ROWNUM in a sub-query (where it can number all the rows without a where clause to restrict it) and then in the outer query perform the test to restrict the rows:

SELECT ename, job
FROM   (
  SELECT ename,
         job,
         ROWNUM AS row_id             -- Generate ROWNUM second.
  FROM   (
    SELECT ename, job
    FROM   Emp
    ORDER BY ename                    -- ORDER BY first.
  )
)
WHERE  MOD( row_id, 2 ) = 0;          -- Filter third.

SQLFIDDLE

5
votes

Try this

To find the EVEN number of row details you can use this code. This one is comfortable in oracle SQL as well as MySQL.

select * from (select ename,job, ROWNUM AS rn from emp) where mod(rn, 2) = 0;

To find the ODD number of row details you can use this code.

select * from (select ename,job, ROWNUM AS rn from emp) where mod(rn, 2) <> 0;
3
votes
-- for selecting rows 1,3,5,7....
SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME FROM 
(
  SELECT DENSE_RANK OVER(ORDER BY EMPLOYEE_ID)AS RANK, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
  FROM EMPLOYEES
)
WHERE MOD(RANK,2)=1

-- for selecting rows 2,4,6,8....
SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME FROM 
(
  SELECT DENSE_RANK OVER(ORDER BY EMPLOYEE_ID)AS RANK, EMPLOYEE_ID, FIRST_NAME, LAST_NAME
  FROM EMPLOYEES
)
WHERE MOD(RANK,2)=0
0
votes
select * from emp where empid in (select decode(mod(rownum,2),0, empid,null) from emp);  --Alternate record Even numbered

Fetch even records from the table

0
votes

Or You can try this:

Here The inner query will fetch all the even records primary key value and based on that we can get all those record details.

SELECT * FROM EMP WHERE EMPNO IN
( 
SELECT CASE MOD(ROWNUM,2)
        WHEN 0 THEN EMPNO
        END
FROM EMP
);

This will fetch all the odd records

SELECT * FROM EMP WHERE EMPNO IN
(
SELECT  CASE MOD(ROWNUM,2)
        WHEN 1 THEN EMPNO
        END
FROM EMP
);
0
votes

this will do :

Select * from (Select rownum as No_of_Row, E.* from emp E)
Where mod(No_of_Row,2)=0;
0
votes

Use the below query u will get the result:

SELECT empno,ename,slno  
FROM  ( SELECT empno,ename,ROW_NUMBER() OVER
(ORDER BY empno desc) AS slno FROM emp ) result WHERE mod(slno,2) = 1;
0
votes

Use the below query,

select * from (select ename,job, ROWNUM as row_id from emp) where mod(row_id, 2) = 0;
0
votes
Select ename, job from emp where mod(id, 2) =0 ;

You can Try the id column intead of rownum if table has an identity column

-3
votes

row num represents row number in the results table not in the original table. So, first assign it to row_id or any variable and use that variable.

-3
votes

Please try :

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

Output:- 2 4 6

Select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

Output: 1,3,5..

Or this will work

this :

odd :

select * from
( 
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber', 
FROM table1
 ) d 
where (RowNumber % 2) = 1

even :

select * from
( 
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1 DESC) AS 'RowNumber', 
FROM table1
) d 
where (RowNumber % 2) = 0