'Sorting and getting position or row nth number of sqlite database for Android studio

I have this table of students

  public void onCreate(SQLiteDatabase db) {

    String createTableStatement= "CREATE TABLE " + STUDENT_TABLES + " (" + ID_COL + " INTEGER PRIMARY KEY AUTOINCREMENT, "
            + STUDENT_NAME_COL + " STRING, " + ENGLISH_CLASS_COL + " FLOAT, "
            + ENGLISH_EXAMS_COL + " FLOAT, "
            + MATHEMATICS_CLASS_COL + " FLOAT, "
            + MATHEMATICS_EXAMS_COL + " FLOAT, "
            + SCIENCE_CLASS_COL + " FLOAT, "
            + SCIENCE_EXAMS_COL + " FLOAT, "
            + SOCIALSTUDIES_CLASS_COL + " FLOAT,"
            + SOCIALSTUDIES_EXAMS_COL + " FLOAT, "
            + RME_CLASS_COL + " FLOAT, "
            + RME_EXAMS_COL + " FLOAT, "
            + BDT_CLASS_COL + " FLOAT, "
            + BDT_EXAMS_COL + " FLOAT, "
            + ICT_CLASS_COL + " FLOAT, "
            + ICT_EXAMS_COL + " FLOAT,"
            + FRENCH_CLASS_COL + " FLOAT, "
            + FRENCH_EXAMS_COL + " FLOAT, "
            + FANTE_CLASS_COL + " FLOAT, "
            + FANTE_EXAMS_COL + " FLOAT, "
            + OTHERS1_CLASS_COL + " FLOAT, "
            + OTHERS1_EXAMS_COL + " FLOAT, "
            + OTHERS2_CLASS_COL + " FLOAT, "
            + OTHERS2_EXAMS_COL + " FLOAT, "
            + TOTALSCORE_COL + " FLOAT) ";


    db.execSQL(createTableStatement);
}

Please how do i get the position of student according to their mark.? example if Mary had 10 marks and Joe had 9 marks the last column of Mary would be 1 or 1st

androidsqldatabase


Solution 1:[1]

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;

Solution 2:[2]

You must use ORDER BY

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stu
Solution 2

Related Questions