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 ?
Answer
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.