How to Select Even Records from a Table in Oracle

how can I fetch even or odd records from a table?

This is because of a peculiarity of rownum. It's a pseudo-column which is applied to the result set returned by the query. (This is why WHERE ROWNUM > 1 is always false.)

In this case the use of ROWNUM causes the query to stop when the WHERE clause returns false. So this returns no rows (because 0 is only returned for even-numbered rows):

select * from employee a 
    where 0 = mod(rownum,2); 

Your second approach has a subquery which doesn't use ROWNUM in a WHERE clause, and so allows the whole table to be returned for evaluation in the outer query.

Any approach which allows the materialization of the entire result set without evaluating ROWNUM will work. This will also produce the result you want:

select * from
    (select a.*, rownum as rn from employee a)
where mod(rn,2) = 1
/

---

As @DavidAldridge points out in his comment, without an ORDER BY clause the result set is essentially random. ROWNUM doesn't play nice with ORDER BY, so to guarantee the ordering use the analytic function ROW_NUMBER() instead.

select * from
    (select a.*
            , row_number() over (order by a.emp_id)  as rn 
     from employee a)
where mod(rn,2) = 0
/

---

" how bellow query fetches only first two records from table."

Through the wonders of the COUNT STOPKEY operation. The query knows how many rows are expected; it returns rows (and assigns values of ROWNUM) until that limit is reached.

We can see this in the EXPLAIN PLAN. Without the filter:

SQL> explain plan for 
  2      select * from emp;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     3 |   111 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |     3 |   111 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

12 rows selected.

SQL> 

Here is the plan with where rownum <= 2 . Note the difference in rows selected:

SQL> explain plan for 
  2      select * from emp
  3      where rownum <= 2;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1973284518

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    74 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |     3 |   111 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=2)

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

SQL> 

Odd Even number in a single Oracle SQL query

You should be able to do this without a subquery. Assuming you just want a label on each row:

SELECT T.C_DATE, T.C_NAME, T.C_TIME, L_UID, ROWNUM as RANK,
       (CASE WHEN MOD(ROWNUM, 2) = 0 THEN 'EVEN' ELSE 'ODD' END) as even_odd_label 
FROM TENTER T
WHERE C_DATE BETWEEN :P_FROM_DATE AND :P_TO_DATE 
ORDER BY L_UID, C_TIME ASC;

Select Rows with id having even number

You are not using Oracle, so you should be using the modulus operator:

SELECT * FROM Orders where OrderID % 2 = 0;

The MOD() function exists in Oracle, which is the source of your confusion.

Have a look at this SO question which discusses your problem.

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

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

How to retrieve half of records from a table - Oracle 11g

Well, you could count the rows and select half:

select *
from   my_table
where  rownum <= (select count(*)/2 from my_table)

That would tend to select rows that are contiguous within the physical segments.

Or ...

select *
from   (select rownum rn, * from my_table)
where  mod(rn,2) = 0

That would tend to select "every other" row, so you'd get a pretty even spread from the physical data segments.

Or ...

select *
from   my_table sample (50)

That would be approximately half of the rows.

Or ...

select *
from   my_table sample block (50)

That would be the rows from approximately half of the data blocks below the high water marks of the segments.

Probably lots of different ways available, and which one you want probably depends on whether you want the selected pseudo-randomly or not.

If you want to use the output of the query, use something like:

select ...
from   (select *
        from   my_table
        where  rownum <= (select count(*)/2 from my_table)) my_table
join   ...

In that circumstance the SAMPLE syntax would be more compact.

How to select columns with an even ID number?

SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID, 2) = 0

---

Related Topics