Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Selecting the first n rows, Pagination, Rownum & ROW_NUMBER() in Oracle

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 690
    Comment on it

    There will be requirement where you might need to select the first n rows, or do pagination in Oracle. This can be achieved using Rownum.

    Lets create sample table and data to understand the logic.

    create table Employees (
      id     number  primary key,
      name   varchar2(30),
      age  number(2)
    );
    
    insert into Employees values ( 1, 'Anand Sharma',     19);
    insert into Employees values ( 2, 'Pankaj Kumar',     22);
    insert into Employees values ( 3, 'Aditya Raturi',    23);
    insert into Employees values ( 4, 'Pawan Shukla',     20);
    insert into Employees values ( 5, 'Poojan Garg',      34);
    insert into Employees values ( 6, 'Amit Sharma',      54);
    insert into Employees values ( 7, 'Chandan Ahluwalia',33);
    insert into Employees values ( 8, 'Ankur Sinha',      34);
    insert into Employees values ( 9, 'Sumit Vyas',       26);
    insert into Employees values (10, 'Nagraj K',         43);
    insert into Employees values (11, 'Praveen Saini',    54);
    insert into Employees values (12, 'Pooja Verma',      56);
    insert into Employees values (13, 'Pramod Pandey',    33);
    insert into Employees values (14, 'Ketan Kumar',      35);
    insert into Employees values (15, 'Akhil Pratap',     53);
    insert into Employees values (16, 'Shaili Sharma',    32);
    insert into Employees values (17, 'Kabir Kumar',      31);
    insert into Employees values (18, 'Chetan Kapoor',    30);
    insert into Employees values (19, 'Esha Khan',        29);
    insert into Employees values (20, 'Kamala Rani',      39);
    insert into Employees values (21, 'Vimal Wasan',      27);
    

    Now let say requirement is to get first 5 records, this can be achieved using following query:

    select name, age from Employees where rownum < 6;
    

    Following, would be the result, please view its same order as it was inserted:

    Anand Sharma    19
    Pankaj Kumar    22
    Aditya Raturi   23
    Pawan Shukla    20
    Poojan Garg     34
    

    Now let say you want to do pagination in your application. For that requirement would be to get data from database in slots. For example, in first page first 10 records are displayed and on second page next 10 records are displayed.

    Query to get first 10 record would be:

    select name, age 
      from (
      select rownum r, name, age 
        from Employees
      )
    where r >  0 and
          r < 11;
    

    And result for it would be:

    Anand Sharma        19
    Pankaj Kumar        22
    Aditya Raturi       23
    Pawan Shukla        20
    Poojan Garg         34
    Amit Sharma         54
    Chandan Ahluwalia   33
    Ankur Sinha         34
    Sumit Vyas          26
    Nagraj K            43
    

    Query to get next 10 records for page 2 would be:

    select name, age 
      from (
      select rownum r, name, age 
        from Employees
      )
    where r >  10 and
          r < 21;
    

    And result for it would be:

    Praveen Saini    54
    Pooja Verma      56
    Pramod Pandey    33
    Ketan Kumar      35
    Akhil Pratap     53
    Shaili Sharma    32
    Kabir Kumar      31
    Chetan Kapoor    30
    Esha Khan        29
    Kamala Rani      39    
    

    Now let say requirement was to have order by age in the query, following would be query for it:

    select name, age
      from (
        select name, age, row&#95;number() over (order by age) r
          from Employees
      )
    where r between 1 and 5; 
    

    And result for it would be:

    Anand Sharma    19
    Pawan Shukla    20
    Pankaj Kumar    22
    Aditya Raturi   23
    Sumit Vyas      26
    

    Please note following query won't give correct order by result which is displayed below:

    select name, age
      from (
        select name, age, rownum r
          from Employees order by age
      )
    where r between 1 and 5; 
    

    Please note though end record should have 'Sumit Vyas' with Age 26, but it is having 'Poojan Garg' Age 34.

    Anand Sharma    19
    Pawan Shukla    20
    Pankaj Kumar    22
    Aditya Ratur    23
    Poojan Garg     34
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: