Tuesday, March 11, 2014

SQL: how to display nth record from table

To display nth row from a table say Emp: 

select * from Emp where rownum< (n+1) MINUS  select * from Emp where rownum < n

First select query will return first N rows and second select query will return first N-1 rows and then subtract these two results which gives only nth row in the table.
It is similar to Minus operation over sets

For example if you want to display 5th row from emp table then:
select * from emp where rownum< 6 MINUS  select * from emp where rownum < 5

SQL: Display 5th row from a table
SQL Minus Operation