Counting the number of rows with a value greater than or equal to a value from another column in SQL

This might not be particularly efficient but should get the job done:

SELECT t1.num_marks,         (SELECT count(t2.couple_id)   FROM table_name t2     WHERE t2.num_marks >= t1.num_marks   ) AS num_couples FROM table_name t1  GROUP BY t1.num_marks   ORDER BY t1.num_marks DESC;

Edit : You can use a sub query in the select, from, where, group by and having clauses of a query, and if you reference the main / outer 'query' then it will evaluate the subquery for each row, then it is known as a correlated subquery. (Hence the caveat about performance)

As per Damien's answer, you could also use a CTE - CTE's can improve readability and also make recursion and self-joins a lot easier IMO.

AFAIK subqueries are supported in most SQL's.

You can use the RANK() function to work out where each result ranks, then just add the number of tied results onto that rank:

create table #T (couple_id int,num_marks int)insert into #T (couple_id,num_marks)select    9 ,         7 union allselect    6 ,         6 union allselect    8 ,         6 union allselect    2 ,         5 union allselect    3 ,         4 union allselect    5 ,         4 union allselect    1 ,         3 union allselect    4 ,         3 union allselect   10 ,         2 union allselect    7 ,         1;with Ranked as (    select num_marks,RANK() OVER (ORDER BY num_marks desc) as rk from #T)select num_marks,rk + COUNT(*) -1 as Result from Rankedgroup by num_marks,rk

Gives:

num_marks   Result----------- --------------------7           16           35           44           63           82           91           10(7 row(s) affected)

(Of course, if you need the results in a particular order, don't forget to add an ORDER BY clause - the above ordering is just a happy accident)