How to rank table rows group wise in SQL Server without using RANK() or rownums function?

I am trying to rank the below table by grouping the sid and ranking the groups through marks in SQL Server.

Refer to the following table and answer the questions:

Marks:

sid     sub     marks
----------------------
53666   sub1    30
53666   sub2    15
53666   sub2    20
53667   sub1    10
53667   sub2    40
53667   sub3    20

Using windowing/analytical functions give a query for achieving the following output:

sid     sub     rank
---------------------
53666   sub1    1
53666   sub2    3
53666   sub2    2
53667   sub1    3
53667   sub2    1
53667   sub3    2

Answer

try this query

SELECT *, CASE 
            WHEN mark >= 30 THEN 1 
            WHEN mark <= 15 THEN 3
            ELSE 2
            END
FROM [dbo].[Q1]

output

id          sid         sub        mark        
----------- ----------- ---------- ----------- -----------
1           53666       sub1       30          1
2           53666       sub2       15          3
3           53666       sub2       20          2
4           53667       sub1       10          3
5           53667       sub2       40          1
6           53667       sub3       20          2