How to get row number from selected rows in Oracle
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
.
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%'