Oracle/SQL: Why does query “SELECT * FROM records WHERE rownum >= 5 AND rownum

by

In Oracle, Rownum values are assigned after the filtering stage of the query – they are not rows of the table, they are rows of the query result set.

So the first row that comes back will always be given rownum 1, the second row that comes back rownum 2, etc.

The rownum value is only incremented after it is assigned, so any query like

select * from t where ROWNUM > 1

will never return any results. This query says ‘I dont want to see the first row that gets returned to me, only the ones after that’ which is sortof a paradox so nothing gets returned.

See Ask Tom:On ROWNUM and Limiting Results for more details.

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. SQL Query to concatenate column values from multiple rows in Oracle
  3. Search All Fields In All Tables For A Specific Value (Oracle)
  4. Select First Row of Every Group in sql [duplicate]
  5. Paging with Oracle
  6. Comparing Dates in Oracle SQL
  7. Oracle Differences between NVL and Coalesce
  8. SQL – How to store and navigate hierarchies?
  9. GROUP BY / aggregate function confusion in SQL
  10. Oracle “(+)” Operator
  11. Calculating difference between two timestamps in Oracle in milliseconds
  12. How does GROUP BY work?
  13. How can I find which tables reference a given table in Oracle SQL Developer?
  14. How to check the maximum number of allowed connections to an Oracle database?
  15. Oracle equivalent of Postgres’ DISTINCT ON?
  16. How to get the last row of an Oracle table
  17. Select without a FROM clause in Oracle
  18. Is there any way to flush output from PL/SQL in Oracle?
  19. What is the difference between ‘YYYY’ and ‘RRRR’ in Oracle SQL
  20. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  21. PLSQL Insert into with subquery and returning clause
  22. SELECT from table with Varying IN list in WHERE clause
  23. How to populate calendar table in Oracle?
  24. Does Oracle use short-circuit evaluation?
  25. Oracle 11g – Check constraint with RegEx
  26. Advice Using Pivot Table in Oracle
  27. ORACLE SQL:Get all integers between two numbers
  28. How can I solve ORA-00911: invalid character error?
  29. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  30. Exporting a CLOB to a text file using Oracle SQL Developer

Leave a Comment