Zahid Zahid - 4 months ago 18
SQL Question

How to select position based on some quantity in sql server

I have two tables :
table 1 Student

+------+-------+
|Roll | Name |
+------+-------+
| 1 | A |
| 2 | B |
| 3 | C |
+------+-------+


table 2 Mark

+------+------+
| Roll | Mark |
+------+------+
| 1 | 85 |
| 3 | 95 |
+------+------+


Output is:

+-------+------+-------+---------+
| Roll | Name | Mark |Position |
+-------+------+-------+---------+
| 1 | A | 85 | 2 |
| 2 | B | 0 | 3 |
| 3 | C | 95 | 1 |
+-------+------+-------+---------+


What should be the query to get the output? I think rank function is to be used,but dont know to use it...

Answer

Use LEFT JOIN to join two tables and then use RANK().

Query

select *, Position = rank() over(
    order by t.Mark desc
)
from(
    select t1.Roll, t1.Name, coalesce(t2.Mark, 0) as Mark
    from student t1
    left join Mark t2
    on t1.Roll = t2.Roll
)t
order by t.Name;