Student subject Positioning based on score using php and mysql database

[sule]

Please I am at a confused I need a code to generate the student position in subject 

SELECT marks.studentid, marks.studentname, marks.acayearid, marks.termid,marks.subjectid, marks.classid, marks.studentid, marks.staffname, marks.class_score2, marks.exam_score2, marks.total, marks.grade, marks.pos, marks.rem,
RANK() OVER (PARTITION BY marks.termid, marks.classid, subjectid ORDER BY marks.total DESC) pos
FROM marks;

[Olumide]

I noticed that you've mentioned pos twice in your SELECT clause. Here's a revised version of your SQL query:

 

SELECT
    marks.studentid,
    marks.studentname,
    marks.acayearid,
    marks.termid,
    marks.subjectid,
    marks.classid,
    marks.staffname,
    marks.class_score2,
    marks.exam_score2,
    marks.total,
    marks.grade,
    RANK() OVER (PARTITION BY marks.termid, marks.classid, marks.subjectid ORDER BY marks.total DESC) as pos,
    marks.rem
FROM
    marks;

 

[sule]

SELECT
    marks.studentid,
    marks.studentname,
    marks.acayearid,
    marks.termid,
    marks.subjectid,
    marks.classid,
    marks.staffname,
    marks.class_score2,
    marks.exam_score2,
    marks.total,
    marks.grade,
    RANK() OVER (PARTITION BY marks.termid, marks.classid, marks.subjectid ORDER BY marks.total DESC) as pos,
    marks.rem
FROM
    marks WHERE marks.subjectid='CREATIVE ARTS' OR marks.subjectid='ENGLISH' OR marks.subjectid='HISTORY' OR marks.subjectid='ICT' OR marks.subjectid='HISTORY' OR marks.subjectid='MATHEMATICS' OR marks.subjectid='SCIENCE' OR marks.subjectid='O. W. O. P.' ORDER BY marks.studentname ASC;

Edited November 18, 2023 by sule

[sule]

[Barand]

Hint: Instead of a string of OR conditions it is much simpler to use IN(...)

WHERE marks.subjectid IN ('CREATIVE ARTS', 'ENGLISH', 'HISTORY', 'ICT', 'MATHEMATICS', 'SCIENCE', 'O. W. O. P.') 

 

[sule]

On 11/18/2023 at 2:38 PM, Barand said:

Hint: Instead of a string of OR conditions it is much simpler to use IN(...)

WHERE marks.subjectid IN ('CREATIVE ARTS', 'ENGLISH', 'HISTORY', 'ICT', 'MATHEMATICS', 'SCIENCE', 'O. W. O. P.') 

 

Still after the query it can only display one student. But i want display all the students in the class. I need your help.

[Barand]

I'd love to help but my query is having difficulty reading that picture of your data.

[phpiodore]

14 hours ago, sule said:

Still after the query it can only display one student. But i want display all the students in the class. I need your help.

What's the code now?

[phpiodore]

13 hours ago, Barand said:

I'd love to help but my query is having difficulty reading that picture of your data.

wait I'm sorry this is wildly off-topic but are you really 74 years old? (if so, that's insanely cool and I'd love to ask you some questions)

Edited November 26, 2023 by phpiodore

[Barand]

28 minutes ago, phpiodore said:

What's the code now?

I won't know until I get some useable data (table dump) to test with. Then I will gladly post a picture of the solution.

[phpiodore]

Just now, Barand said:

I won't know until I get some useable data (table dump) to test with. Then I will gladly post a picture of the solution.

Oh, sorry, I must have misquoted you by mistake -- I was asking the original poster for the current version of the code