How can I Rank() the value of a column based on

Question

In SQL, how can I rank() based on values on a column based on condition of another column?

I have a table like this. How can I rank based on value of 'NO' if Class is 2?

The source is :

No	Name	Class
101	reema	2
102	kriya	1
103	meena	3
104	carlin	1
105	dhiren	2
106	hiren	2
107	mahir	3
108	nishi	1

The result

No	Name	Class	Rank
101	reema	2	1
102	kriya	1
103	meena	3
104	carlin	1
105	dhiren	2	2
106	hiren	2	3
107	mahir	3
108	nishi	1

Answer 1

You can rank studens within each class with row_number() and partition by class.

To ignore classes you are not interested in, a case expression comes handy:

select t.*,
    case when class = 2 
        then row_number() over(partition by class order by no) 
    end as rnk
from mytable t