[sql] 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 ?

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%';

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.

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;

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%'