Home » SQL & PL/SQL » SQL & PL/SQL » rownum from table
rownum from table [message #670506] |
Mon, 09 July 2018 21:41 |
|
suji6281
Messages: 135 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 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
suji6281 wrote on Mon, 09 July 2018 19:41Hi 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 #670511 is a reply to message #670510] |
Mon, 09 July 2018 22:28 |
|
suji6281
Messages: 135 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 |
|
Barbara Boehmer
Messages: 9097 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 |
cookiemonster
Messages: 13925 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 |
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;
|
|
|
Goto Forum:
Current Time: Tue Jun 18 00:26:59 CDT 2024
|