RowNum
select rownum,e.* from HR.employees e;
The order is proper but when we add a order by clause the rownum becomes messy
select rownum,e.* from HR.employees e order by FIRST_NAME;
- This is because the rownum is execute first and after which the order by clause is execurted which messes up the rownum order.
- To fix this we should create a query without rownum first and then encapsuldate the result a inline view, example below
select rownum, result.* from (select e.* from HR.employees e order by FIRST_NAME) result;
You can use ROWNUM to limit the number of rows returned by a query, as in this example:
SELECT * FROM employees WHERE ROWNUM < 10;
RowId
For each row in the database, the ROWID pseudocolumn returns the address of the row.
- They are the fastest way to access a single row.
- They can show you how the rows in a table are stored.
- They are unique identifiers for rows in a table.
Example :
SELECT ROWID, last_name FROM employees WHERE department_id = 20;