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..
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
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;
-- 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With