Home » SQL & PL/SQL » SQL & PL/SQL » rownum from table
rownum from table [message #670506] Mon, 09 July 2018 21:41 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Team,

Could you please help me with sql query to return row number from the table.
I want to know the specific field value row number in the table.
Example as shown below.

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);


INSERT INTO PERSONS VALUES ('56742', 'John', 'Smith', 'street no12', 'Malven');
INSERT INTO PERSONS VALUES ('56785', 'John1', 'Smith1', 'street no12', 'Malven');

SELECT ROWNUM FROM PERSONS WHERE PERSONID = '56785';

It should return row number as 2 but it is showing 1 from above select statement.

Please help me how to return exact row number with sql query.

Thank you.

Regards
Suji
Re: rownum from table [message #670507 is a reply to message #670506] Mon, 09 July 2018 21:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
suji6281 wrote on Mon, 09 July 2018 19:41
Hi Team,

Could you please help me with sql query to return row number from the table.
I want to know the specific field value row number in the table.
Example as shown below.

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);


INSERT INTO PERSONS VALUES ('56742', 'John', 'Smith', 'street no12', 'Malven');
INSERT INTO PERSONS VALUES ('56785', 'John1', 'Smith1', 'street no12', 'Malven');

SELECT ROWNUM FROM PERSONS WHERE PERSONID = '56785';

It should return row number as 2 but it is showing 1 from above select statement.

Please help me how to return exact row number with sql query.

Thank you.

Regards
Suji
rows in a table are like balls in a basket.
Which ball is #2 ball in the basket?

The requirement is 100% nonsensical.

With SELECT statement returning only 1 row, then how can rownum equal 2?
Re: rownum from table [message #670508 is a reply to message #670507] Mon, 09 July 2018 21:57 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Blackswan,

I would like to know the personid 56785 exists in which row in the table. I'm sorry if I confused you regarding this requirement.

Thank you.

Regards
Suji
Re: rownum from table [message #670510 is a reply to message #670508] Mon, 09 July 2018 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
suji6281 wrote on Mon, 09 July 2018 19:57
Hi Blackswan,

I would like to know the personid 56785 exists in which row in the table. I'm sorry if I confused you regarding this requirement.

Thank you.

Regards
Suji
Rows in a table have NO order.
ROWNUM is assigned only after the row has successfully passed through the WHERE clause & can be impacted by ORDER BY clause.

which row in table is where EMPNO=7566 based upon EMP table below

SQL>  select * from emp order by 1;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10

SQL>  select * from emp order by 2;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10

SQL>  select * from emp order by 3;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10

SQL>  select * from emp order by 4;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10

SQL>  select * from emp order by 5;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10

SQL> 

Re: rownum from table [message #670511 is a reply to message #670510] Mon, 09 July 2018 22:28 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi Blackswan,

Thanks for your reply with detail example. Now I have included one more field id_number in PERSONS table and we can use it for order by clause.

Hope this table and data will help you to get specific field row number using sql.

CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255) ,
Id_number int
);


INSERT INTO PERSONS VALUES (56742, 'John', 'Smith', 'street no12', 'Malven', 321);
INSERT INTO PERSONS VALUES (56785, 'John1', 'Smith1', 'street no12', 'Malven', 322);

SELECT ROWNUM FROM PERSONS WHERE PERSONID = 56785;

Thank you.

Regards
Suji
Re: rownum from table [message #670512 is a reply to message #670506] Mon, 09 July 2018 23:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
ROWNUM is a pseudo-column used to number rows in a result set. If you do not specify an order, hen the rows may be in any random order. If you first specify an order, then apply ROWNUM from an outer query, then the ROWNUM is in that order previously specified. If you only have 1 row in your result set, then there is only one ROWNUM and it will be 1.

So, first you order all of the rows:
SCOTT@orcl_12.1.0.2.0> SELECT PersonID
  2  FROM   PERSONS
  3  ORDER  BY PersonID
  4  /

  PERSONID
----------
     56742
     56785

2 rows selected.

-- then you apply ROWNUM from an outer query after ordering in the inner sub-query
-- giving it an alias (rownum1) that can be used from another outer query:
SCOTT@orcl_12.1.0.2.0> SELECT ROWNUM rownum1, PersonID
  2  FROM   (SELECT PersonID
  3  	     FROM   PERSONS
  4  	     ORDER  BY PersonID)
  5  /

   ROWNUM1   PERSONID
---------- ----------
         1      56742
         2      56785

2 rows selected.

-- then you can select from another outer query, using that alias,
-- limiting the rows in the where clause:
SCOTT@orcl_12.1.0.2.0> SELECT rownum1, PersonID
  2  FROM   (SELECT ROWNUM rownum1, PersonID
  3  	     FROM   (SELECT PersonID
  4  		     FROM   PERSONS
  5  		     ORDER  BY PersonID))
  6  WHERE  PersonID = 56785
  7  /

   ROWNUM1   PERSONID
---------- ----------
         2      56785

1 row selected.

-- If you were to apply ROWNUM again from an outer query that only has one record in the result set,
-- then the ROWNUM at that level (rownum2) can only be 1:
SCOTT@orcl_12.1.0.2.0> SELECT ROWNUM rownum2, rownum1, PersonID
  2  FROM   (SELECT ROWNUM rownum1, PersonID
  3  	     FROM   (SELECT PersonID
  4  		     FROM   PERSONS
  5  		     ORDER  BY PersonID))
  6  WHERE  PersonID = 56785
  7  /

   ROWNUM2    ROWNUM1   PERSONID
---------- ---------- ----------
         1          2      56785

1 row selected.

Re: rownum from table [message #670514 is a reply to message #670512] Tue, 10 July 2018 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And just to be really clear - rownum is not a pseudo-column of a table, it is a pseudo column of a result set.
SQL> select p.rownum from persons p;
select p.rownum from persons p

ORA-01747: invalid user.table.column, table.column, or column specification
The persons table, and all other tables don't have rownum.
But a result set does:
SQL> select p.personid, rownum from persons p, persons;

                               PERSONID     ROWNUM
--------------------------------------- ----------
                                  56742          1
                                  56742          2
                                  56785          3
                                  56785          4

SQL> 
In this rownum doesn't relate to any specific row from the persons table because each row is duplicated by a cartesian join.
Rownum relates to the combined rows in the result set.
Re: rownum from table [message #670517 is a reply to message #670514] Tue, 10 July 2018 06:06 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
There is a hack using analytics that will do what you want. Try the following using the row_number analytic
select row_num
from
(select row_number() over (order by personid) row_num,PersonID
from persons)
where personid = 56785;
Previous Topic: How can bulk insert into the parent , child tables
Next Topic: SELECT QUERY
Goto Forum:
  


Current Time: Fri Mar 29 08:43:27 CDT 2024