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

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

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

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

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

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

Examples related to sql

Examples related to oracle

Examples related to rownum