18

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 ?

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Tushar
  • 231
  • 1
  • 4
  • 8

4 Answers4

44

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
13

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;
DazzaL
  • 21,638
  • 3
  • 49
  • 57
5

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%'
Ben
  • 51,770
  • 36
  • 127
  • 149
boyce
  • 61
  • 1
  • 1
    Hi, and welcome to Stack Overflow. You can mark-up code by highlighting it and clicking the `{}` button. Take some time to lean the markdown syntax as it makes your post appear a lot easier to read. I have two points on your actual answer; firstly it's completely covered by the answer's already here. Secondly, your first query returns the rows in a random order so it does not guarantee the order requested in the question. – Ben Jan 28 '13 at 10:07
2

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%';
Srinivasan.S
  • 3,065
  • 1
  • 24
  • 15