How to get row number from selected rows in Oracle

Question

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 ?

Answer 1

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.