Sorting and and getting position or row nth number of sql database for Android studio-CodePudding

I have an sql database table which contain student's exams marks. I want a query which will display students position in class according to the mark he or she got(1st or second etc).

|Student | Mark|

|Mary | 9|

|Joe | 12|

|Annabelle | 10|

How can i query to get Joe as 1 or first or interms of index 0?

I want the answer in sql but i will use it in android studio

CodePudding user response：

SELECT Student, Mark FROM tablename ORDER BY Mark DESC

CodePudding user response：

If yo are after an ordinal position like 1st, 2nd, 3rd etc you can use rank/dense_rank window functions assuming your database supports standard analytic functions:

with students as (
 select 'Mary' Student, 9 Mark union all
 select 'Joe', 12 union all
 select 'Annabelle', 10
)

select Student, Rank() over(order by Mark desc) Position
from students
order by Position;

CodePudding user response：

You must use ORDER BY

SELECT *, ROW_NUMBER() OVER (ORDER BY mark DESC) pos FROM student