Selecting the Second Row of a Table Using Rownum

Selecting the second row of a table using rownum

To explain this behaviour, we need to understand how Oracle processes
ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and
only increments the value when a row is selected; that is, when all
conditions in the WHERE clause are met. Since our condition requires
that ROWNUM is greater than 2, no rows are selected and ROWNUM is
never incremented beyond 1.

The bottom line is that conditions such as the following will work as
expected.

.. WHERE rownum = 1;

.. WHERE rownum <= 10;

While queries with these conditions will always return zero rows.

.. WHERE rownum = 2;

.. WHERE rownum > 10;

Quoted from Understanding Oracle rownum

You should modify you query in this way in order to work:

select empno
from
    (
    select empno, rownum as rn 
    from (
          select empno
          from emp
          order by sal desc
          )
    )
where rn=2;

EDIT: I've corrected the query to get the rownum after the order by sal desc

Select n'th row using ROWNUM Oracle

In the second query, you are not using rownum but a fixed value: rn. Its values are computed in the subquery.

In the first query, the second rownum is not the same rownum as the rownum in the subselect. The rownum in the subselect works for the rowset in the subselect. And the rownum in the outer query works for the outer query. So, your first query is seen by oracle like:

SELECT SALARY
FROM something
WHERE ROWNUM >= N;
RETURN result;

This won't give records because first row is rownum = 1 and its not >= N. And the second row fetched its now the first and it's not >= N. And so on.

See here another question with the same issue.

If you want, the rownum is the last thing computed. It is a assigned when a row is fetched. So, always the first row has rownum = 1. :)

Select Nth Row From A Table In Oracle

Based on the classic answer:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= N_ROWS )
 where rnum >= N_ROWS
/

retrieve only second row of 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

Retrieve specific rows without using rownum

If you want to avoid rownum and row_number, use sum:

select *
from (
        select sum(1) over ( order by rowid /* or whatever you need */ ) as rn,
               r.*
          from record
     )
where rn between 2 and 4

The trick is only in the fact that here sum(1) gives the same thing than count(1) or count(rowid) or whatever count on a not null value, and this is the same thing than counting the rows with row_number or rownum.
In this way you use the sum to compute a row_number, without explicitly writing 'row_number' or 'rownum'.

SQL> create table testTab(x) as ( select level from dual connect by level <= 6);

Table created.

SQL> select t.*,
  2         count(1)     over (order by rowid desc) as count,
  3         sum(1)       over (order by rowid desc) as sum,
  4         row_number() over (order by rowid desc) as rowNumber
  5  from testTab t;

         X      COUNT        SUM  ROWNUMBER
---------- ---------- ---------- ----------
         6          1          1          1
         5          2          2          2
         4          3          3          3
         3          4          4          4
         2          5          5          5
         1          6          6          6

The external query simply applies the filter.

Accessing second row in result

Please check:

SELECT * FROM
(
    SELECT DEPT_ID, COUNT(*) AS stud_count, ROW_NUMBER() over (order by COUNT(*) desc) ROW_NUM
    FROM TBL_STUDENT_DEPARTMENT_593932       
        GROUP BY DEPT_ID
)
WHERE ROW_NUM = 2;

oracle sql wih rownum =

Rownum has a special meaning in Oracle. It is increased with every row, but the optimizer knows that is increasing continuously and all consecutive rows must met the rownum condition. So if you specify rownum = 2 it will never occur since the first row is already rejected.

You can see this very nice if you do an explain plan on your query. It will show something like:

Plan for rownum <=:

COUNT STOPKEY       

Plan for rownum =:

FILTER

How to select the nth row in a SQL database table?

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

T-SQL How to select only Second row from a table?

Assuming SQL Server 2005+ an example of how to get just the second row (which I think you may be asking - and is the reason why top won't work for you?)

set statistics io on

;with cte as
(
  select *
    , ROW_NUMBER() over (order by number) as rn
  from master.dbo.spt_values
) 
select *
from cte
where rn = 2

/* Just to add in what I was running RE: Comments */
;with cte as
(
  select top 2 *
    , ROW_NUMBER() over (order by number) as rn
  from master.dbo.spt_values
) 
select *
from cte
where rn = 2

---

Related Topics