Finding rank of the student -Sql Compact

Question

I have a table like this :

Name Mar1 Mar2 Mar3 Total

xxx 80 80 80 240

yyy 60 70 50 180

aaa 85 65 75 225

I wanted to find the rank of the student based on total. I using SQL Compact 3.5 . As we have rank() function in sql server do we have something with which we can find the students rank??? When I used "select Total,rank() over (order by total desc) i1 from stmarks " it's giving error as

" Major Error 0x80040E14, Minor Error 25501

select Total,rank() over (order by total desc) i1 from stmarks There was an error parsing the query. [ Token line number = 1,Token line offset = 21,Token in error = over ] "

Do Sql Compact support rank() over or is there any another way???

Answer 1

According to the search result it seems over() doesn't work with sql-compact. So I tried the self join concept as in here. Just I modified the query so that it works with duplicate values properly . Remove the equal to sign. The modified query is :

SELECT a1.Name, a1.Total, COUNT(a2.Total) Rank
  FROM StMarks a1, StMarks a2
 WHERE a1.Total < a2.Total or (a1.Total=a2.Total and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Total
ORDER BY a1.Total DESC, a1.Name DESC;

It works great rite now.