How to get row number from selected rows in Oracle

Question

I select several rows from the database, for example:

select * from student where name is like %ram% 

Result:

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

I need to get the line number for which branch is B5. Expected Value - "5"

Can anyone suggest how to implement this in a query?

Answer 1

There is no built-in ordering in the table. Thus, the line number itself is a meaningless metric.

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

Since there is no need for an explicit ORDER BY clause to order the table in order to work.

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

or using an analytical request

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

Your syntax where name is like ... incorrect, there is no need for IS, so I deleted it.

Here ORDER BY relies on binary sorting, so if the branch starts with something other than B, the results may be different, for example, b greater than b .

Answer 2

you can just do

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

this assigns the line number as the lines are retrieved (so there is no guaranteed ordering, of course).

if you want to make the first order:

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

Answer 3

I think using

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

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

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

Answer 4

The following query helps get the line number in oracle,

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