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

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 

CodePudding user response：

SELECT Student, Mark FROM tablename ORDER BY Mark DESC