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    rama gmail com    B1 5    ramb    ramb gmail com    B2 3    ramc    ramc gmail com    B3 8    ramd    ramd gmail com    B4 11   rame    rame gmail com    B5 12   ramf    ramf gmail com    B6 14   ramg    ramg gmail com    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

User · Answer

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

User · 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

User · Answer

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

User · Answer

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

[sql] How to get row number from selected rows in Oracle

Examples related to sql

Examples related to oracle

Examples related to rownum