sule Posted November 17, 2023 Share Posted November 17, 2023 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; Quote Link to comment Share on other sites More sharing options...
Olumide Posted November 18, 2023 Share Posted November 18, 2023 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; Quote Link to comment Share on other sites More sharing options...
sule Posted November 18, 2023 Author Share Posted November 18, 2023 (edited) 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 Quote Link to comment Share on other sites More sharing options...
sule Posted November 18, 2023 Author Share Posted November 18, 2023 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 18, 2023 Share Posted November 18, 2023 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.') Quote Link to comment Share on other sites More sharing options...
sule Posted November 25, 2023 Author Share Posted November 25, 2023 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 25, 2023 Share Posted November 25, 2023 I'd love to help but my query is having difficulty reading that picture of your data. Quote Link to comment Share on other sites More sharing options...
phpiodore Posted November 26, 2023 Share Posted November 26, 2023 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? Quote Link to comment Share on other sites More sharing options...
phpiodore Posted November 26, 2023 Share Posted November 26, 2023 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 26, 2023 Share Posted November 26, 2023 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. Quote Link to comment Share on other sites More sharing options...
phpiodore Posted November 26, 2023 Share Posted November 26, 2023 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.