Thank you for your reply.

Here is my try till now, but not what I am wants :

CREATE TABLE #TempTable (Rownumber INTEGER IDENTITY(1, 1),ROLLNO INTEGER,NAME VARCHAR(30),CLASS VARCHAR(30),RNK INTEGER)

SET IDENTITY_INSERT #TempTable OFF

INSERT into #TempTable

select rollno,name,class,

(CASE

WHEN class = 'IT' THEN 1

WHEN class = 'Science' THEN 2

WHEN class = 'Arts' THEN 3

WHEN class = 'Commerce' THEN 4

WHEN class = 'Geography' THEN 5

ELSE 6

END) as rnk

from student

ORDER BY RNK

select z.rollno,min(z.name),min(z.class),min(z.rownumber)

from

(

SELECT ROLLNO, t.name,t.class,t.Rownumber,(t.Rownumber - o.Offset) AS XX,rnk

FROM #TempTable t

INNER JOIN (

SELECT class, MIN(Rownumber) - 1 AS Offset

FROM #TempTable

GROUP BY class

) o ON o.class = t.class

--order by t.rownumber,t.rollno

) z

group by z.rollno

DROP TABLE #TempTable