开发者

retrieve only second row of the table in oracle?

开发者 https://www.devze.com 2023-02-22 06:18 出处:网络
Can anyone help, ho开发者_如何学Gow to retrieve exactly 2nd row from the table in oracle? Since the rows in a table are inherently unordered, the concept of \"first\" and \"second\" requires that you

Can anyone help, ho开发者_如何学Gow to retrieve exactly 2nd row from the table in oracle?


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


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消