how to select even records from a table in oracle?

by

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

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. I want to find out how much zero come after 1 in SQL
  3. Splitting string into multiple rows in Oracle
  4. GROUP BY with MAX(DATE) [duplicate]
  5. How to use the ‘as’ keyword to alias a table in Oracle?
  6. Can a foreign key be NULL and/or duplicate?
  7. Stored Procedure with optional “WHERE” parameters
  8. subquery in FROM must have an alias
  9. SQL multiple columns in IN clause
  10. How can I select from list of values in Oracle
  11. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  12. Selecting the second row of a table using rownum
  13. sqlplus statement from command line
  14. Does Oracle store trailing zeroes for Number data type?
  15. Oracle Update Query using Join
  16. How to convert Rows to Columns in Oracle? [duplicate]
  17. SQL: getting the max value of one column and the corresponding other columns [duplicate]
  18. Autoincrement in oracle to already created table
  19. SQL not displaying null values on a not equals query?
  20. What can cause an Oracle ROWID to change?
  21. How to add ‘ON DELETE CASCADE’ in ALTER TABLE statement
  22. Oracle query to match all values in the list among all rows in table
  23. Inserting into Oracle and retrieving the generated sequence ID
  24. adding primary key to sql view
  25. How do I call an Oracle stored procedure from an Excel VBA script?
  26. Return number from Oracle Select statement after parsing date
  27. How do you interpret a query’s explain plan?
  28. How to call Oracle MD5 hash function?
  29. SQL Server equivalent of WM_CONCAT function [duplicate]
  30. Oracle: ‘= ANY()’ vs. ‘IN ()’

Leave a Comment