CODEWITHSUNDEEP
oracle-database
Venkat
Venkat

Reputation: 21480

retrieve only second row of the table in oracle?

Can anyone help, how to retrieve exactly 2nd row from the table in oracle?

Upvotes: 3

Views: 8664

Answers (2)

Venkat
Venkat

Reputation: 21480

Thanks for your answers,now i found the solution for this,

      select * from
        (select rownum rn,column1,column2,...,columnn from tablename)
      where
         rn=2

Now you can check this and post your valuable comments.

Upvotes: -5

Justin Cave
Justin Cave

Reputation: 231661

Since the rows in a table are inherently unordered, the concept of "first" and "second" requires that you specify some way of enforcing order (i.e. an ORDER BY clause). The simplest way to do this is to use an analytic function

SELECT *
  FROM (SELECT a.*,
               row_number() OVER (ORDER BY some_column) rn
          FROM your_table a)
 WHERE rn = 2;

You could also use ROWNUM though that requires an additional level of nesting

SELECT *
  FROM (SELECT b.*, rownum rn
          FROM (SELECT *
                  FROM your_table a
                 ORDER BY some_column) b
         WHERE rownum <= 2)
 WHERE rn > 1

Upvotes: 11

Related Questions