CODEWITHSUNDEEP
sqlsql-server
quantum62
quantum62

Reputation: 153

find rank of row

I have a table with some records that I want to check rank of rows and if the rank is greater than 100 I will set permission.
I wrote flowing code to find rank of records but when I used the rank or row_number function without an additional field (like username or family) it doesn't work though it does works with one of the additional fields.

SELECT * from
(Select username,
Rank() over(order by point desc) as  'ranking' 
from student) t
 where (username='test')

I want to find a record and set a variable with rank of this record.

Upvotes: 0

Views: 184

Answers (3)

Loïc bcn
Loïc bcn

Reputation: 154

May be this query ?

Select count(*)+1 as rank from student 
where point > (select point from student where username = 'test')

Wich should return the number of user with less points than the selected user

Upvotes: 0

techBeginner
techBeginner

Reputation: 3850

For RANK to work, you need to specify PARTITION BY column over which you want to Rank over, and then, ORDER BY is like if there comes a situation like both the Rank columns have the same value and there is a conflict, then order by column will sort out the issue by ordering those conflicting records..

Upvotes: 0

dani herrera
dani herrera

Reputation: 51655

I don't know if your sqlser version supprts cte. If it is a >= 2005 sqlserver this is your query:

create table #student ( username varchar(10), point int );

insert into #student values
( 'a', 10 ),
( 'b', 20 ),
( 'test', 15 );

declare @var int;

with cte as (
  select username,
  Rank() over(order by point desc) as  [ranking] 
  from #student
)
select  @var = [ranking]  from cte where username = 'test';
print str( @var );

Results:

2

Upvotes: 1

Related Questions