CODEWITHSUNDEEP
sqloracle-databaserownum
Tushar
Tushar

Reputation: 261

How to get row number from selected rows in Oracle

I am selecting few rows from database e.g.:

select * from student where name is like %ram%

Result:

ID   Name     email          Branch
7    rama    [email protected]    B1
5    ramb    [email protected]    B2
3    ramc    [email protected]    B3
8    ramd    [email protected]    B4
11   rame    [email protected]    B5
12   ramf    [email protected]    B6
14   ramg    [email protected]    B7

I need to get row number for which branch is B5. Expected value is "5"

Can someone please suggest How to implement this in query ?

Upvotes: 21

Views: 228144

Answers (4)

Srinivasan.S
Srinivasan.S

Reputation: 3143

The below query helps to get the row number in oracle,

SELECT ROWNUM AS SNO,ID,NAME,EMAIL,BRANCH FROM student WHERE NAME LIKE '%ram%';

Upvotes: 2

boyce
boyce

Reputation: 61

I think using

select rownum st.Branch 
  from student st 
 where st.name like '%ram%'

is a simple way; you should add single quotes in the LIKE statement. If you use row_number(), you should add over (order by 'sort column' 'asc/desc'), for instance:

select st.branch, row_number() over (order by 'sort column' 'asc/desc')  
  from student st 
 where st.name like '%ram%'

Upvotes: 5

Ben
Ben

Reputation: 52913

There is no inherent ordering to a table. So, the row number itself is a meaningless metric.

However, you can get the row number of a result set by using the ROWNUM psuedocolumn or the ROW_NUMBER() analytic function, which is more powerful.

As there is no ordering to a table both require an explicit ORDER BY clause in order to work.

select rownum, a.*
  from ( select *
           from student
          where name like '%ram%'
          order by branch
                ) a

or using the analytic query

select row_number() over ( order by branch ) as rnum, a.*
  from student
 where name like '%ram%'

Your syntax where name is like ... is incorrect, there's no need for the IS, so I've removed it.

The ORDER BY here relies on a binary sort, so if a branch starts with anything other than B the results may be different, for instance b is greater than B.

Upvotes: 50

DazzaL
DazzaL

Reputation: 21993

you can just do 

select rownum, l.* from student  l where name like %ram%

this assigns the row number as the rows are fetched (so no guaranteed ordering of course).

if you wanted to order first do:

select rownum, l.*
  from (select * from student l where name like %ram% order by...) l;

Upvotes: 13

Related Questions