Student subject Positioning based on score using php and mysql database

[Ponel]

Thanks I grab.  arms(This is the level arms a student belong i.e. A,B,C,D....) while armsLevel (This is the Level a student belong to i.e JS1, JS2, JS3, ...). 

So a student can belong to JS1A,  JS1B, JS1C, JS2A, JS2B, JS2C .......

Edited December 30, 2020 by Ponel

[Ponel]

Good morning sir Mr Barand. Thanks for yesterday. I've tried the remaining script of the subject position and it works well. Thank you.

I want to use the same approach to get the rank of overall student which will include the rank and number of student in a class i.e. 1st of out 30 students (Note: the 1st is the overall rank of a student in a class while the 30 is the number of students in a class)

This is the query i wrote, To get this, I will need to Sum the total score of a student in a class for a particular semester, year, level and arms and then rank it among students.

 SELECT yearid
     , semesterid
     , regno
     , subjectid
     , rank
     , SUM(total)
     , grade
     , comment
FROM (     
        SELECT yearid
             , semesterid
             , subjectid
             , @seq := CASE WHEN yss <> @prev THEN 1
                           ELSE @seq + 1
                           END as seq
             , @rank := CASE WHEN total = @prevtot THEN @rank
                             ELSE @seq
                             END as rank
             , @prevtot := total as total
             , @prev := yss as yss
             , regno
             , armsLevelId                                            -- added
             , armsid                                                 -- added
        FROM (
                SELECT yearid
                     , semesterid
                     , subjectid
                     , concat(yearid, semesterid, subjectid) as yss
                     , regno
                     , total
                     , armsLevelId                                   -- added
                     , armsid                                        -- added
                FROM subject_position
                ORDER BY yearid, semesterid, subjectid, total DESC
             ) sorted
             JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
    ) ranked
    JOIN
    grade ON total BETWEEN grade.lomark AND grade.himark
WHERE regNo='4663'                                                   -- A  
    and armsLevelId='1'                                              --  D
    and armsId='1'                                                   --   D 
    and semesterid='1'                                               --    E 
    and yearid='1'                                                   --     D   
ORDER BY subjectid     

for user 4663

for user 6073

Instead of user 4663 take rank 2 while user 6073 take on rank 1. 

 

Any clue on this sir

 

Edited December 30, 2020 by Ponel

[Barand]

1 ) You want to rank the total score so you need to get that in the lowest level subquery

2) you can't apply the grade to the total score (ranges don't apply). I have applied it to the students' average scores.

Hopefully, this is what you want...

+--------+------------+-------+-------+------+----------------+---------------+-----------+
| yearid | semesterid | regno | total | rank | Total students | Average grade | comment   |
+--------+------------+-------+-------+------+----------------+---------------+-----------+
|      1 |          1 |  4663 |   865 | 2    |              2 | B2            | V Good    |
|      1 |          1 |  6073 |   969 | 1    |              2 | A             | Excellent |
+--------+------------+-------+-------+------+----------------+---------------+-----------+

Query

SELECT yearid
     , semesterid
     , regno
     , total
     , rank
     , numstudents as `Total students`
     , grade as `Average grade`
     , comment
FROM (     
        SELECT yearid
             , semesterid
             , @seq := CASE WHEN yss <> @prev THEN 1
                           ELSE @seq + 1
                           END as seq
             , @rank := CASE WHEN total = @prevtot THEN @rank
                             ELSE @seq
                             END as rank
             , @prevtot := total as total
             , @prev := yss as yss
             , regno
             , armsLevelId
             , armsid
             , avgtotal
        FROM (
                SELECT yearid
                     , semesterid
                     , subjectid
                     , concat(yearid, semesterid, armsid, armsLevelId) as yss
                     , regno
                     , SUM(total) as total
                     , ROUND(AVG(total)) as avgtotal
                     , armsLevelId
                     , armsid
                FROM subject_position
                GROUP BY yearid, semesterid, armsid, armsLevelId,regno
                ORDER BY yearid, semesterid, armsid, armsLevelId, total DESC
             ) sorted
             JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
    ) ranked
    JOIN 
    grade ON avgtotal BETWEEN grade.lomark and grade.himark
    JOIN (
           SELECT yearid
                 , semesterid
                 , armsLevelId
                 , armsid
                 , COUNT(DISTINCT regno) as numstudents
           FROM subject_position
           GROUP BY yearid, semesterid, armsid, armsLevelId
         ) students    USING (yearid, semesterid, armsid, armsLevelId)
WHERE
--  regno = 4663 and                                               -- uncomment for individual student
    armsLevelId='1'
    and armsId='1'
    and semesterid='1'
    and yearid='1' 
ORDER BY regno;

 

[Ponel]

Yeah this is what i wanted. I got it thanks.. I need to study the query the is superb. Thank you sir. I appreciate 

 

Edited December 30, 2020 by Ponel

[Barand]

I haven't used a "student" table, but yes, you should have one. I am surprised you don't have one already (it's one of you basic entities).

(I do have a subquery with the alias "student" which contains the total students for each group.)

 

Suggested:

                    +----------------+                                                        +-------------+
                    | student        |                                                        | subject     |
                    +----------------+                                                        +-------------+
                    | regno  PK      |---------+                                   +----------| subjectid PK|
                    | firstname      |         |                                   |          | subject     |
                    | lastname       |         |           +-------------+         |          +-------------+
                    | armsid         |         |           | result      |         |
                    | armslevelid    |         |           +-------------+         |
                    +----------------+         |           | result_id PK|         |
                                               +----------<| regno       |         |
+--------------+                                 +--------<| year        |         |
| year         |                                 +--------<| semester    |         |
+--------------+                                 |         | subjectid   |>--------+
| yearid    PK |-----+                           |         | result_type |
| startdate    |     |    +---------------+      |         | pcent       |
| enddate      |     |    | semester      |      |         +-------------+
+--------------+     |    +---------------+      |
                     +---<| yearid     PK |>-----+
                          | semester   PK |>-----+
                          | startdate     |
                          | enddate       |
                          +---------------+ 

 

[Ponel]

I have student table Already sir. if i want to calculate the overall semesterid, yearid, armslevelid, armsid. and rank the student for 1st term, 2nd term and 3rd term result. this is my query

 

SELECT yearid
     , semesterid
     , regno
     , total
     , rank
     , numstudents as `Total students`
     , grade as `Average grade`
     , comment
FROM (     
        SELECT yearid
             , semesterid
             , @seq := CASE WHEN yss <> @prev THEN 1
                           ELSE @seq + 1
                           END as seq
             , @rank := CASE WHEN total = @prevtot THEN @rank
                             ELSE @seq
                             END as rank
             , @prevtot := total as total
             , @prev := yss as yss
             , regno
             , armsLevelId
             , armsid
             , avgtotal
        FROM (
                SELECT yearid
                     , semesterid
                     , subjectid
                     , concat(yearid, semesterid, armsid, armsLevelId) as yss
                     , regno
                     , SUM(total) as total
                     , ROUND(AVG(total)) as avgtotal
                     , armsLevelId
                     , armsid
                FROM subject_position
                GROUP BY yearid, semesterid, armsid, armsLevelId,regno
                ORDER BY yearid, semesterid, armsid, armsLevelId, total DESC
             ) sorted
             JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
    ) ranked
    JOIN 
    grade ON avgtotal BETWEEN grade.lomark and grade.himark
    JOIN (
           SELECT yearid
                 , semesterid
                 , armsLevelId
                 , armsid
                 , COUNT(DISTINCT regno) as numstudents
           FROM subject_position
           GROUP BY yearid, semesterid, armsid, armsLevelId
         ) students    USING (yearid, semesterid, armsid, armsLevelId)
WHERE
--  regno = 4663 and                                               -- uncomment for individual student
    armsLevelId='1'
    and armsId='1'
    and semesterid='1'
    and semesterid='2'
    and semesterid='3'
    and yearid='1' 
ORDER BY regno;

 

but i got empty row. Any clue on this sir?

 

[Barand]

Quote

    and semesterid='1'
    and semesterid='2'
    and semesterid='3'

How many records have you where the semesterid is equal 1 and 2 and 3 all at the same time?

Try

and semesterid in (1,2,3)

i.e. 1 OR 2 OR 3

PS as those are the only three possible values, you could just leave that condition out ot the query.

Edited December 30, 2020 by Barand

[Ponel]

I want to select and rank all records where semesterid is 1,2,3 which will allow me to compute the annual ranking 

[Barand]

then remove the semester from the groupings, so you get totals by year only

[Ponel]

Alright sir. I will try it out

[Barand]

In fact, remove reference to semester completely from the query. As you are no longer grouping by it, any values will be arbitrary and meaningless.

[Ponel]

I wrote this by removing all the semesterid in the where clause and i got this output

SELECT yearid
, semesterid
, regno
, total
, rank
, numstudents as `Total students`
, grade as `Average grade`
, comment
FROM (     
   SELECT yearid
        , semesterid
        , @seq := CASE WHEN yss <> @prev THEN 1
                      ELSE @seq + 1
                      END as seq
        , @rank := CASE WHEN total = @prevtot THEN @rank
                        ELSE @seq
                        END as rank
        , @prevtot := total as total
        , @prev := yss as yss
        , regno
        , armsLevelId
        , armsid
        , avgtotal
   FROM (
           SELECT yearid
                , semesterid
                , subjectid
                , concat(yearid, semesterid, armsid, armsLevelId) as yss
                , regno
                , SUM(total) as total
                , ROUND(AVG(total)) as avgtotal
                , armsLevelId
                , armsid
           FROM subject_position
           GROUP BY yearid, semesterid, armsid, armsLevelId,regno
           ORDER BY yearid, semesterid, armsid, armsLevelId, total DESC
        ) sorted
        JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
) ranked
JOIN 
grade ON avgtotal BETWEEN grade.lomark and grade.himark
JOIN (
      SELECT yearid
            , semesterid
            , armsLevelId
            , armsid
            , COUNT(DISTINCT regno) as numstudents
      FROM subject_position
      GROUP BY yearid, semesterid, armsid, armsLevelId
    ) students    USING (yearid, semesterid, armsid, armsLevelId)
WHERE
regno = '6073'                                                -- uncomment for individual student
and armsLevelId='1'
and armsId='1' 
and yearid='1' 
ORDER BY regno;

 

 

what i wanted is the summation of the total i.e for user 4663 overall score (903+873+733 = 2509) while user 6073 overall score (977+974+777 = 2728) then rank the total. 

[Barand]

You missed a couple

[Ponel]

Bravo.. This worked thanks Mr Barand for your guide and time. God bless you Abundantly sir

[Ponel]

Compliment of the season Mr Barand. Thank you for your guide so far. I appreciate that. Please i wanted to ask, how can one resolve ties in ranking?

I have the table

DROP TABLE IF EXISTS `subject_position`;

CREATE TABLE IF NOT EXISTS `subject_position` (

          `id` int(11) NOT NULL AUTO_INCREMENT,

           `regNo` int(50) NOT NULL,

            `subjectid` int(50) NOT NULL,

            `armsLevelId` int(50) NOT NULL,

            `armsId` int(50) NOT NULL,

            `semesterid` int(50) NOT NULL,

             `yearid` int(50) NOT NULL,

              `total` int(50) NOT NULL,

               PRIMARY KEY (`id`)

)

 

INSERT INTO `subject_position` (`id`, `regNo`, `subjectid`, `armsLevelId`, `armsId`, `semesterid`, `yearid`, `total`) VALUES

(1, 2183, 1, 1, 1, 1, 10, 60),

(2, 4253, 1, 1, 1, 1, 10, 94),

(3, 8248, 1, 1, 1, 1, 10, 94),

(4, 7032, 1, 1, 1, 1, 10, 60),

(5, 9390, 1, 1, 1, 1, 10, 0),

(6, 461, 1, 1, 1, 1, 10, 0),

(7, 5072, 1, 1, 1, 1, 10, 50),

(8, 3487, 1, 1, 1, 1, 10, 59),

(9, 5938, 1, 1, 1, 1, 10, 65),

(10, 7120, 1, 1, 1, 1, 10, 81),

(11, 1352, 1, 1, 1, 1, 10, 88),

(12, 4965, 1, 1, 1, 1, 10, 95),

(13, 5896, 1, 1, 1, 1, 10, 65),

(14, 9490, 1, 1, 1, 1, 10, 0),

(15, 8835, 1, 1, 1, 1, 10, 88),

(16, 4167, 1, 1, 1, 1, 10, 88),

(17, 350, 1, 1, 1, 1, 10, 88),

(18, 8255, 1, 1, 1, 1, 10, 82),

(19, 4621, 1, 1, 1, 1, 10, 100),

(20, 1011, 1, 1, 1, 1, 10, 82),

(21, 6805, 1, 1, 1, 1, 10, 79),

(22, 2808, 1, 1, 1, 1, 10, 70),

(23, 9966, 1, 1, 1, 1, 10, 83),

(24, 9738, 1, 1, 1, 1, 10, 43),

(25, 2111, 1, 1, 1, 1, 10, 94),

(26, 7833, 1, 1, 1, 1, 10, 99),

(27, 5038, 1, 1, 1, 1, 10, 69),

(28, 5897, 1, 1, 1, 1, 10, 63),

(29, 6377, 1, 1, 1, 1, 10, 76),

(30, 9208, 1, 1, 1, 1, 10, 96),

(31, 9491, 1, 1, 1, 1, 10, 90),

(32, 4984, 1, 1, 1, 1, 10, 87),

(33, 8836, 1, 1, 1, 1, 10, 70),

(34, 1398, 1, 1, 1, 1, 10, 85),

(35, 2183, 2, 1, 1, 1, 10, 67),

(36, 4253, 2, 1, 1, 1, 10, 94),

(37, 8248, 2, 1, 1, 1, 10, 98),

(38, 7032, 2, 1, 1, 1, 10, 85),

(39, 9390, 2, 1, 1, 1, 10, 0),

(40, 461, 2, 1, 1, 1, 10, 0),

(41, 5072, 2, 1, 1, 1, 10, 68),

(42, 3487, 2, 1, 1, 1, 10, 71),

(43, 5938, 2, 1, 1, 1, 10, 59),

(44, 7120, 2, 1, 1, 1, 10, 85),

(45, 1352, 2, 1, 1, 1, 10, 88),

(46, 4965, 2, 1, 1, 1, 10, 97),

(47, 5896, 2, 1, 1, 1, 10, 68),

(48, 9490, 2, 1, 1, 1, 10, 67),

(49, 8835, 2, 1, 1, 1, 10, 86),

(50, 4167, 2, 1, 1, 1, 10, 83),

(51, 350, 2, 1, 1, 1, 10, 95),

(52, 8255, 2, 1, 1, 1, 10, 75),

(53, 4621, 2, 1, 1, 1, 10, 99),

(54, 1011, 2, 1, 1, 1, 10, 88),

(55, 6805, 2, 1, 1, 1, 10, 75),

(56, 2808, 2, 1, 1, 1, 10, 78),

(57, 9966, 2, 1, 1, 1, 10, 84),

(58, 9738, 2, 1, 1, 1, 10, 52),

(59, 2111, 2, 1, 1, 1, 10, 97),

(60, 7833, 2, 1, 1, 1, 10, 100),

(61, 5038, 2, 1, 1, 1, 10, 68),

(62, 5897, 2, 1, 1, 1, 10, 80),

(63, 6377, 2, 1, 1, 1, 10, 75),

(64, 9208, 2, 1, 1, 1, 10, 96),

(65, 9491, 2, 1, 1, 1, 10, 85),

(66, 4984, 2, 1, 1, 1, 10, 86),

(67, 8836, 2, 1, 1, 1, 10, 0),

(68, 1398, 2, 1, 1, 1, 10, 77),

(69, 2183, 6, 1, 1, 1, 10, 93),

(70, 4253, 6, 1, 1, 1, 10, 100),

(71, 8248, 6, 1, 1, 1, 10, 100),

(72, 7032, 6, 1, 1, 1, 10, 0),

(73, 9390, 6, 1, 1, 1, 10, 0),

(74, 461, 6, 1, 1, 1, 10, 0),

(75, 5072, 6, 1, 1, 1, 10, 87),

(76, 3487, 6, 1, 1, 1, 10, 87),

(77, 5938, 6, 1, 1, 1, 10, 90),

(78, 7120, 6, 1, 1, 1, 10, 94),

(79, 1352, 6, 1, 1, 1, 10, 98),

(80, 4965, 6, 1, 1, 1, 10, 100),

(81, 5896, 6, 1, 1, 1, 10, 92),

(82, 9490, 6, 1, 1, 1, 10, 86),

(83, 8835, 6, 1, 1, 1, 10, 100),

(84, 4167, 6, 1, 1, 1, 10, 100),

(85, 350, 6, 1, 1, 1, 10, 98),

(86, 8255, 6, 1, 1, 1, 10, 96),

(87, 4621, 6, 1, 1, 1, 10, 100),

(88, 1011, 6, 1, 1, 1, 10, 99),

(89, 6805, 6, 1, 1, 1, 10, 100),

(90, 2808, 6, 1, 1, 1, 10, 90),

(91, 9966, 6, 1, 1, 1, 10, 100),

(92, 9738, 6, 1, 1, 1, 10, 78),

(93, 2111, 6, 1, 1, 1, 10, 100),

(94, 7833, 6, 1, 1, 1, 10, 100),

(95, 5038, 6, 1, 1, 1, 10, 93),

(96, 5897, 6, 1, 1, 1, 10, 98),

(97, 6377, 6, 1, 1, 1, 10, 95),

(98, 9208, 6, 1, 1, 1, 10, 100),

(99, 9491, 6, 1, 1, 1, 10, 98),

(100, 4984, 6, 1, 1, 1, 10, 99),

(101, 8836, 6, 1, 1, 1, 10, 0),

(102, 1398, 6, 1, 1, 1, 10, 87),

(103, 3378, 1, 2, 1, 1, 10, 51),

(104, 3641, 1, 2, 1, 1, 10, 88),

(105, 3679, 1, 2, 1, 1, 10, 0),

(106, 6970, 1, 2, 1, 1, 10, 81),

(107, 8127, 1, 2, 1, 1, 10, 90),

(108, 2451, 1, 2, 1, 1, 10, 93),

(109, 3628, 1, 2, 1, 1, 10, 89),

(110, 2393, 1, 2, 1, 1, 10, 82),

(111, 8410, 1, 2, 1, 1, 10, 78),

(112, 9207, 1, 2, 1, 1, 10, 65),

(113, 3391, 1, 2, 1, 1, 10, 92),

(114, 2660, 1, 2, 1, 1, 10, 76),

(115, 9321, 1, 2, 1, 1, 10, 71),

(116, 7664, 1, 2, 1, 1, 10, 96),

(117, 4800, 1, 2, 1, 1, 10, 75),

(118, 6982, 1, 2, 1, 1, 10, 91),

(119, 8552, 1, 2, 1, 1, 10, 78),

(120, 330, 1, 2, 1, 1, 10, 64),

(121, 8038, 1, 2, 1, 1, 10, 76),

(122, 9844, 1, 2, 1, 1, 10, 75),

(123, 8113, 1, 2, 1, 1, 10, 0),

(124, 4252, 1, 2, 1, 1, 10, 74),

(125, 7566, 1, 2, 1, 1, 10, 98),

(126, 2544, 1, 2, 1, 1, 10, 74),

(127, 531, 1, 2, 1, 1, 10, 68),

(128, 9569, 1, 2, 1, 1, 10, 81),

(129, 7699, 1, 2, 1, 1, 10, 96),

(130, 3378, 2, 2, 1, 1, 10, 50),

(131, 3641, 2, 2, 1, 1, 10, 50),

(132, 3679, 2, 2, 1, 1, 10, 0),

(133, 6970, 2, 2, 1, 1, 10, 65),

(134, 8127, 2, 2, 1, 1, 10, 93),

(135, 2451, 2, 2, 1, 1, 10, 95),

(136, 3628, 2, 2, 1, 1, 10, 90),

(137, 2393, 2, 2, 1, 1, 10, 68),

(138, 8410, 2, 2, 1, 1, 10, 78),

(139, 9207, 2, 2, 1, 1, 10, 85),

(140, 3391, 2, 2, 1, 1, 10, 91),

(141, 2660, 2, 2, 1, 1, 10, 85),

(142, 9321, 2, 2, 1, 1, 10, 65),

(143, 7664, 2, 2, 1, 1, 10, 80),

(144, 4800, 2, 2, 1, 1, 10, 74),

(145, 6982, 2, 2, 1, 1, 10, 90),

(146, 8552, 2, 2, 1, 1, 10, 50),

(147, 330, 2, 2, 1, 1, 10, 63),

(148, 8038, 2, 2, 1, 1, 10, 61),

(149, 9844, 2, 2, 1, 1, 10, 60),

(150, 8113, 2, 2, 1, 1, 10, 0),

(151, 4252, 2, 2, 1, 1, 10, 68),

(152, 7566, 2, 2, 1, 1, 10, 100),

(153, 2544, 2, 2, 1, 1, 10, 67),

(154, 531, 2, 2, 1, 1, 10, 95),

(155, 9569, 2, 2, 1, 1, 10, 70),

(156, 7699, 2, 2, 1, 1, 10, 100),

(157, 3378, 3, 2, 1, 1, 10, 95),

(158, 3641, 3, 2, 1, 1, 10, 80),

(159, 3679, 3, 2, 1, 1, 10, 0),

(160, 6970, 3, 2, 1, 1, 10, 85),

(161, 8127, 3, 2, 1, 1, 10, 85),

(162, 2451, 3, 2, 1, 1, 10, 95),

(163, 3628, 3, 2, 1, 1, 10, 100),

(164, 2393, 3, 2, 1, 1, 10, 82),

(165, 8410, 3, 2, 1, 1, 10, 88),

(166, 9207, 3, 2, 1, 1, 10, 75),

(167, 3391, 3, 2, 1, 1, 10, 100),

(168, 2660, 3, 2, 1, 1, 10, 80),

(169, 9321, 3, 2, 1, 1, 10, 80),

(170, 7664, 3, 2, 1, 1, 10, 100),

(171, 4800, 3, 2, 1, 1, 10, 90),

(172, 6982, 3, 2, 1, 1, 10, 80),

(173, 8552, 3, 2, 1, 1, 10, 75),

(174, 330, 3, 2, 1, 1, 10, 88),

(175, 8038, 3, 2, 1, 1, 10, 85),

(176, 9844, 3, 2, 1, 1, 10, 80),

(177, 8113, 3, 2, 1, 1, 10, 0),

(178, 4252, 3, 2, 1, 1, 10, 95),

(179, 7566, 3, 2, 1, 1, 10, 98),

(180, 2544, 3, 2, 1, 1, 10, 80),

(181, 531, 3, 2, 1, 1, 10, 88),

(182, 9569, 3, 2, 1, 1, 10, 85),

(183, 7699, 3, 2, 1, 1, 10, 100),

(184, 3378, 4, 2, 1, 1, 10, 80),

(185, 3641, 4, 2, 1, 1, 10, 68),

(186, 3679, 4, 2, 1, 1, 10, 0),

(187, 6970, 4, 2, 1, 1, 10, 78),

(188, 8127, 4, 2, 1, 1, 10, 100),

(189, 2451, 4, 2, 1, 1, 10, 100),

(190, 3628, 4, 2, 1, 1, 10, 90),

(191, 2393, 4, 2, 1, 1, 10, 78),

(192, 8410, 4, 2, 1, 1, 10, 70),

(193, 9207, 4, 2, 1, 1, 10, 60),

(194, 3391, 4, 2, 1, 1, 10, 100),

(195, 2660, 4, 2, 1, 1, 10, 100),

(196, 9321, 4, 2, 1, 1, 10, 55),

(197, 7664, 4, 2, 1, 1, 10, 95),

(198, 4800, 4, 2, 1, 1, 10, 85),

(199, 6982, 4, 2, 1, 1, 10, 90),

(200, 8552, 4, 2, 1, 1, 10, 50),

(201, 330, 4, 2, 1, 1, 10, 65),

(202, 8038, 4, 2, 1, 1, 10, 68),

(203, 9844, 4, 2, 1, 1, 10, 50),

(204, 8113, 4, 2, 1, 1, 10, 0),

(205, 4252, 4, 2, 1, 1, 10, 85),

(206, 7566, 4, 2, 1, 1, 10, 100),

(207, 2544, 4, 2, 1, 1, 10, 80),

(208, 531, 4, 2, 1, 1, 10, 90),

(209, 9569, 4, 2, 1, 1, 10, 55),

(210, 7699, 4, 2, 1, 1, 10, 100),

(211, 3378, 5, 2, 1, 1, 10, 100),

(212, 3641, 5, 2, 1, 1, 10, 88),

(213, 3679, 5, 2, 1, 1, 10, 0),

(214, 6970, 5, 2, 1, 1, 10, 88),

(215, 8127, 5, 2, 1, 1, 10, 100),

(216, 2451, 5, 2, 1, 1, 10, 100),

(217, 3628, 5, 2, 1, 1, 10, 88),

(218, 2393, 5, 2, 1, 1, 10, 85),

(219, 8410, 5, 2, 1, 1, 10, 90),

(220, 9207, 5, 2, 1, 1, 10, 79),

(221, 3391, 5, 2, 1, 1, 10, 100),

(222, 2660, 5, 2, 1, 1, 10, 98),

(223, 9321, 5, 2, 1, 1, 10, 90),

(224, 7664, 5, 2, 1, 1, 10, 100),

(225, 4800, 5, 2, 1, 1, 10, 100),

(226, 6982, 5, 2, 1, 1, 10, 100),

(227, 8552, 5, 2, 1, 1, 10, 78),

(228, 330, 5, 2, 1, 1, 10, 100),

(229, 8038, 5, 2, 1, 1, 10, 90),

(230, 9844, 5, 2, 1, 1, 10, 80),

(231, 8113, 5, 2, 1, 1, 10, 0),

(232, 4252, 5, 2, 1, 1, 10, 99),

(233, 7566, 5, 2, 1, 1, 10, 100),

(234, 2544, 5, 2, 1, 1, 10, 85),

(235, 531, 5, 2, 1, 1, 10, 90),

(236, 9569, 5, 2, 1, 1, 10, 100),

(237, 7699, 5, 2, 1, 1, 10, 100),

(238, 3378, 6, 2, 1, 1, 10, 60),

(239, 3641, 6, 2, 1, 1, 10, 93),

(240, 3679, 6, 2, 1, 1, 10, 0),

(241, 6970, 6, 2, 1, 1, 10, 90),

(242, 8127, 6, 2, 1, 1, 10, 90),

(243, 2451, 6, 2, 1, 1, 10, 100),

(244, 3628, 6, 2, 1, 1, 10, 83),

(245, 2393, 6, 2, 1, 1, 10, 70),

(246, 8410, 6, 2, 1, 1, 10, 100),

(247, 9207, 6, 2, 1, 1, 10, 100),

(248, 3391, 6, 2, 1, 1, 10, 95),

(249, 2660, 6, 2, 1, 1, 10, 100),

(250, 9321, 6, 2, 1, 1, 10, 90),

(251, 7664, 6, 2, 1, 1, 10, 85),

(252, 4800, 6, 2, 1, 1, 10, 70),

(253, 6982, 6, 2, 1, 1, 10, 80),

(254, 8552, 6, 2, 1, 1, 10, 80),

(255, 330, 6, 2, 1, 1, 10, 80),

(256, 8038, 6, 2, 1, 1, 10, 90),

(257, 9844, 6, 2, 1, 1, 10, 45),

(258, 8113, 6, 2, 1, 1, 10, 0),

(259, 4252, 6, 2, 1, 1, 10, 80),

(260, 7566, 6, 2, 1, 1, 10, 100),

(261, 2544, 6, 2, 1, 1, 10, 55),

(262, 531, 6, 2, 1, 1, 10, 66),

(263, 9569, 6, 2, 1, 1, 10, 65),

(264, 7699, 6, 2, 1, 1, 10, 80),

(265, 3378, 24, 2, 1, 1, 10, 75),

(266, 3641, 24, 2, 1, 1, 10, 76),

(267, 3679, 24, 2, 1, 1, 10, 0),

(268, 6970, 24, 2, 1, 1, 10, 69),

(269, 8127, 24, 2, 1, 1, 10, 90),

(270, 2451, 24, 2, 1, 1, 10, 86),

(271, 3628, 24, 2, 1, 1, 10, 76),

(272, 2393, 24, 2, 1, 1, 10, 73),

(273, 8410, 24, 2, 1, 1, 10, 66),

(274, 9207, 24, 2, 1, 1, 10, 55),

(275, 3391, 24, 2, 1, 1, 10, 80),

(276, 2660, 24, 2, 1, 1, 10, 64),

(277, 9321, 24, 2, 1, 1, 10, 71),

(278, 7664, 24, 2, 1, 1, 10, 100),

(279, 4800, 24, 2, 1, 1, 10, 82),

(280, 6982, 24, 2, 1, 1, 10, 86),

(281, 8552, 24, 2, 1, 1, 10, 96),

(282, 330, 24, 2, 1, 1, 10, 57),

(283, 8038, 24, 2, 1, 1, 10, 72),

(284, 9844, 24, 2, 1, 1, 10, 60),

(285, 8113, 24, 2, 1, 1, 10, 0),

(286, 4252, 24, 2, 1, 1, 10, 80),

(287, 7566, 24, 2, 1, 1, 10, 91),

(288, 2544, 24, 2, 1, 1, 10, 77),

(289, 531, 24, 2, 1, 1, 10, 66),

(290, 9569, 24, 2, 1, 1, 10, 55),

(291, 7699, 24, 2, 1, 1, 10, 87),

(292, 3378, 27, 2, 1, 1, 10, 70),

(293, 3641, 27, 2, 1, 1, 10, 50),

(294, 3679, 27, 2, 1, 1, 10, 0),

(295, 6970, 27, 2, 1, 1, 10, 60),

(296, 8127, 27, 2, 1, 1, 10, 90),

(297, 2451, 27, 2, 1, 1, 10, 70),

(298, 3628, 27, 2, 1, 1, 10, 90),

(299, 2393, 27, 2, 1, 1, 10, 58),

(300, 8410, 27, 2, 1, 1, 10, 70),

(301, 9207, 27, 2, 1, 1, 10, 60),

(302, 3391, 27, 2, 1, 1, 10, 100),

(303, 2660, 27, 2, 1, 1, 10, 70),

(304, 9321, 27, 2, 1, 1, 10, 77),

(305, 7664, 27, 2, 1, 1, 10, 70),

(306, 4800, 27, 2, 1, 1, 10, 58),

(307, 6982, 27, 2, 1, 1, 10, 70),

(308, 8552, 27, 2, 1, 1, 10, 70),

(309, 330, 27, 2, 1, 1, 10, 95),

(310, 8038, 27, 2, 1, 1, 10, 60),

(311, 9844, 27, 2, 1, 1, 10, 56),

(312, 8113, 27, 2, 1, 1, 10, 0),

(313, 4252, 27, 2, 1, 1, 10, 65),

(314, 7566, 27, 2, 1, 1, 10, 85),

(315, 2544, 27, 2, 1, 1, 10, 65),

(316, 531, 27, 2, 1, 1, 10, 65),

(317, 9569, 27, 2, 1, 1, 10, 60),

(318, 7699, 27, 2, 1, 1, 10, 80),

(319, 3378, 28, 2, 1, 1, 10, 95),

(320, 3641, 28, 2, 1, 1, 10, 87),

(321, 3679, 28, 2, 1, 1, 10, 0),

(322, 6970, 28, 2, 1, 1, 10, 82),

(323, 8127, 28, 2, 1, 1, 10, 90),

(324, 2451, 28, 2, 1, 1, 10, 95),

(325, 3628, 28, 2, 1, 1, 10, 78),

(326, 2393, 28, 2, 1, 1, 10, 87),

(327, 8410, 28, 2, 1, 1, 10, 95),

(328, 9207, 28, 2, 1, 1, 10, 73),

(329, 3391, 28, 2, 1, 1, 10, 95),

(330, 2660, 28, 2, 1, 1, 10, 85),

(331, 9321, 28, 2, 1, 1, 10, 79),

(332, 7664, 28, 2, 1, 1, 10, 100),

(333, 4800, 28, 2, 1, 1, 10, 85),

(334, 6982, 28, 2, 1, 1, 10, 90),

(335, 8552, 28, 2, 1, 1, 10, 74),

(336, 330, 28, 2, 1, 1, 10, 45),

(337, 8038, 28, 2, 1, 1, 10, 81),

(338, 9844, 28, 2, 1, 1, 10, 69),

(339, 8113, 28, 2, 1, 1, 10, 0),

(340, 4252, 28, 2, 1, 1, 10, 95),

(341, 7566, 28, 2, 1, 1, 10, 100),

(342, 2544, 28, 2, 1, 1, 10, 72),

(343, 531, 28, 2, 1, 1, 10, 95),

(344, 9569, 28, 2, 1, 1, 10, 66),

(345, 7699, 28, 2, 1, 1, 10, 100),

(346, 3378, 29, 2, 1, 1, 10, 75),

(347, 3641, 29, 2, 1, 1, 10, 55),

(348, 3679, 29, 2, 1, 1, 10, 0),

(349, 6970, 29, 2, 1, 1, 10, 75),

(350, 8127, 29, 2, 1, 1, 10, 84),

(351, 2451, 29, 2, 1, 1, 10, 80),

(352, 3628, 29, 2, 1, 1, 10, 65),

(353, 2393, 29, 2, 1, 1, 10, 70),

(354, 8410, 29, 2, 1, 1, 10, 65),

(355, 9207, 29, 2, 1, 1, 10, 90),

(356, 3391, 29, 2, 1, 1, 10, 88),

(357, 2660, 29, 2, 1, 1, 10, 90),

(358, 9321, 29, 2, 1, 1, 10, 60),

(359, 7664, 29, 2, 1, 1, 10, 75),

(360, 4800, 29, 2, 1, 1, 10, 85),

(361, 6982, 29, 2, 1, 1, 10, 100),

(362, 8552, 29, 2, 1, 1, 10, 50),

(363, 330, 29, 2, 1, 1, 10, 85),

(364, 8038, 29, 2, 1, 1, 10, 65),

(365, 9844, 29, 2, 1, 1, 10, 55),

(366, 8113, 29, 2, 1, 1, 10, 0),

(367, 4252, 29, 2, 1, 1, 10, 70),

(368, 7566, 29, 2, 1, 1, 10, 98),

(369, 2544, 29, 2, 1, 1, 10, 78),

(370, 531, 29, 2, 1, 1, 10, 90),

(371, 9569, 29, 2, 1, 1, 10, 60),

(372, 7699, 29, 2, 1, 1, 10, 70),

(373, 3576, 1, 3, 1, 1, 10, 85),

(374, 9079, 1, 3, 1, 1, 10, 66),

(375, 6409, 1, 3, 1, 1, 10, 87),

(376, 9197, 1, 3, 1, 1, 10, 88),

(377, 8660, 1, 3, 1, 1, 10, 78),

(378, 137, 1, 3, 1, 1, 10, 70),

(379, 1477, 1, 3, 1, 1, 10, 78),

(380, 7057, 1, 3, 1, 1, 10, 98),

(381, 4608, 1, 3, 1, 1, 10, 93),

(382, 3576, 2, 3, 1, 1, 10, 60),

(383, 9079, 2, 3, 1, 1, 10, 93),

(384, 6409, 2, 3, 1, 1, 10, 98),

(385, 9197, 2, 3, 1, 1, 10, 100),

(386, 8660, 2, 3, 1, 1, 10, 97),

(387, 137, 2, 3, 1, 1, 10, 90),

(388, 1477, 2, 3, 1, 1, 10, 96),

(389, 7057, 2, 3, 1, 1, 10, 100),

(390, 4608, 2, 3, 1, 1, 10, 100),

(391, 3576, 3, 3, 1, 1, 10, 85),

(392, 9079, 3, 3, 1, 1, 10, 60),

(393, 6409, 3, 3, 1, 1, 10, 98),

(394, 9197, 3, 3, 1, 1, 10, 98),

(395, 8660, 3, 3, 1, 1, 10, 95),

(396, 137, 3, 3, 1, 1, 10, 80),

(397, 1477, 3, 3, 1, 1, 10, 98),

(398, 7057, 3, 3, 1, 1, 10, 100),

(399, 4608, 3, 3, 1, 1, 10, 95),

(400, 3576, 4, 3, 1, 1, 10, 85),

(401, 9079, 4, 3, 1, 1, 10, 75),

(402, 6409, 4, 3, 1, 1, 10, 90),

(403, 9197, 4, 3, 1, 1, 10, 94),

(404, 8660, 4, 3, 1, 1, 10, 93),

(405, 137, 4, 3, 1, 1, 10, 85),

(406, 1477, 4, 3, 1, 1, 10, 87),

(407, 7057, 4, 3, 1, 1, 10, 90),

(408, 4608, 4, 3, 1, 1, 10, 94),

(409, 3576, 5, 3, 1, 1, 10, 55),

(410, 9079, 5, 3, 1, 1, 10, 88),

(411, 6409, 5, 3, 1, 1, 10, 87),

(412, 9197, 5, 3, 1, 1, 10, 91),

(413, 8660, 5, 3, 1, 1, 10, 80),

(414, 137, 5, 3, 1, 1, 10, 70),

(415, 1477, 5, 3, 1, 1, 10, 90),

(416, 7057, 5, 3, 1, 1, 10, 86),

(417, 4608, 5, 3, 1, 1, 10, 85),

(418, 3576, 6, 3, 1, 1, 10, 85),

(419, 9079, 6, 3, 1, 1, 10, 90),

(420, 6409, 6, 3, 1, 1, 10, 75),

(421, 9197, 6, 3, 1, 1, 10, 80),

(422, 8660, 6, 3, 1, 1, 10, 85),

(423, 137, 6, 3, 1, 1, 10, 70),

(424, 1477, 6, 3, 1, 1, 10, 99),

(425, 7057, 6, 3, 1, 1, 10, 95),

(426, 4608, 6, 3, 1, 1, 10, 98),

(427, 3576, 24, 3, 1, 1, 10, 70),

(428, 9079, 24, 3, 1, 1, 10, 93),

(429, 6409, 24, 3, 1, 1, 10, 88),

(430, 9197, 24, 3, 1, 1, 10, 90),

(431, 8660, 24, 3, 1, 1, 10, 92),

(432, 137, 24, 3, 1, 1, 10, 70),

(433, 1477, 24, 3, 1, 1, 10, 93),

(434, 7057, 24, 3, 1, 1, 10, 88),

(435, 4608, 24, 3, 1, 1, 10, 90),

(436, 3576, 27, 3, 1, 1, 10, 100),

(437, 9079, 27, 3, 1, 1, 10, 80),

(438, 6409, 27, 3, 1, 1, 10, 80),

(439, 9197, 27, 3, 1, 1, 10, 100),

(440, 8660, 27, 3, 1, 1, 10, 75),

(441, 137, 27, 3, 1, 1, 10, 80),

(442, 1477, 27, 3, 1, 1, 10, 100),

(443, 7057, 27, 3, 1, 1, 10, 100),

(444, 4608, 27, 3, 1, 1, 10, 100),

(445, 3576, 28, 3, 1, 1, 10, 88),

(446, 9079, 28, 3, 1, 1, 10, 81),

(447, 6409, 28, 3, 1, 1, 10, 93),

(448, 9197, 28, 3, 1, 1, 10, 92),

(449, 8660, 28, 3, 1, 1, 10, 93),

(450, 137, 28, 3, 1, 1, 10, 65),

(451, 1477, 28, 3, 1, 1, 10, 82),

(452, 7057, 28, 3, 1, 1, 10, 91),

(453, 4608, 28, 3, 1, 1, 10, 93),

(454, 936, 1, 4, 1, 1, 10, 95),

(455, 8664, 1, 4, 1, 1, 10, 90),

(456, 7177, 1, 4, 1, 1, 10, 85),

(457, 971, 1, 4, 1, 1, 10, 80),

(458, 5007, 1, 4, 1, 1, 10, 90),

(459, 9515, 1, 4, 1, 1, 10, 95),

(460, 9978, 1, 4, 1, 1, 10, 85),

(461, 6782, 1, 4, 1, 1, 10, 95),

(462, 2828, 1, 4, 1, 1, 10, 75),

(463, 936, 2, 4, 1, 1, 10, 90),

(464, 8664, 2, 4, 1, 1, 10, 100),

(465, 7177, 2, 4, 1, 1, 10, 100),

(466, 971, 2, 4, 1, 1, 10, 83),

(467, 5007, 2, 4, 1, 1, 10, 100),

(468, 9515, 2, 4, 1, 1, 10, 100),

(469, 9978, 2, 4, 1, 1, 10, 95),

(470, 6782, 2, 4, 1, 1, 10, 95),

(471, 2828, 2, 4, 1, 1, 10, 85),

(472, 936, 3, 4, 1, 1, 10, 35),

(473, 8664, 3, 4, 1, 1, 10, 90),

(474, 7177, 3, 4, 1, 1, 10, 85),

(475, 971, 3, 4, 1, 1, 10, 85),

(476, 5007, 3, 4, 1, 1, 10, 90),

(477, 9515, 3, 4, 1, 1, 10, 90),

(478, 9978, 3, 4, 1, 1, 10, 90),

(479, 6782, 3, 4, 1, 1, 10, 85),

(480, 2828, 3, 4, 1, 1, 10, 70),

(481, 936, 4, 4, 1, 1, 10, 85),

(482, 8664, 4, 4, 1, 1, 10, 80),

(483, 7177, 4, 4, 1, 1, 10, 85),

(484, 971, 4, 4, 1, 1, 10, 85),

(485, 5007, 4, 4, 1, 1, 10, 75),

(486, 9515, 4, 4, 1, 1, 10, 90),

(487, 9978, 4, 4, 1, 1, 10, 85),

(488, 6782, 4, 4, 1, 1, 10, 85),

(489, 2828, 4, 4, 1, 1, 10, 80),

(490, 936, 5, 4, 1, 1, 10, 90),

(491, 8664, 5, 4, 1, 1, 10, 90),

(492, 7177, 5, 4, 1, 1, 10, 85),

(493, 971, 5, 4, 1, 1, 10, 65),

(494, 5007, 5, 4, 1, 1, 10, 90),

(495, 9515, 5, 4, 1, 1, 10, 85),

(496, 9978, 5, 4, 1, 1, 10, 90),

(497, 6782, 5, 4, 1, 1, 10, 95),

(498, 2828, 5, 4, 1, 1, 10, 95),

(499, 936, 6, 4, 1, 1, 10, 90),

(500, 8664, 6, 4, 1, 1, 10, 90),

(501, 7177, 6, 4, 1, 1, 10, 80),

(502, 971, 6, 4, 1, 1, 10, 60),

(503, 5007, 6, 4, 1, 1, 10, 90),

(504, 9515, 6, 4, 1, 1, 10, 90),

(505, 9978, 6, 4, 1, 1, 10, 90),

(506, 6782, 6, 4, 1, 1, 10, 100),

(507, 2828, 6, 4, 1, 1, 10, 80),

(508, 936, 24, 4, 1, 1, 10, 90),

(509, 8664, 24, 4, 1, 1, 10, 95),

(510, 7177, 24, 4, 1, 1, 10, 85),

(511, 971, 24, 4, 1, 1, 10, 95),

(512, 5007, 24, 4, 1, 1, 10, 90),

(513, 9515, 24, 4, 1, 1, 10, 90),

(514, 9978, 24, 4, 1, 1, 10, 90),

(515, 6782, 24, 4, 1, 1, 10, 95),

(516, 2828, 24, 4, 1, 1, 10, 90),

(517, 936, 26, 4, 1, 1, 10, 90),

(518, 8664, 26, 4, 1, 1, 10, 90),

(519, 7177, 26, 4, 1, 1, 10, 90),

(520, 971, 26, 4, 1, 1, 10, 90),

(521, 5007, 26, 4, 1, 1, 10, 90),

(522, 9515, 26, 4, 1, 1, 10, 90),

(523, 9978, 26, 4, 1, 1, 10, 90),

(524, 6782, 26, 4, 1, 1, 10, 85),

(525, 2828, 26, 4, 1, 1, 10, 90),

(526, 936, 27, 4, 1, 1, 10, 90),

(527, 8664, 27, 4, 1, 1, 10, 95),

(528, 7177, 27, 4, 1, 1, 10, 90),

(529, 971, 27, 4, 1, 1, 10, 80),

(530, 5007, 27, 4, 1, 1, 10, 90),

(531, 9515, 27, 4, 1, 1, 10, 80),

(532, 9978, 27, 4, 1, 1, 10, 80),

(533, 6782, 27, 4, 1, 1, 10, 80),

(534, 2828, 27, 4, 1, 1, 10, 80),

(535, 936, 28, 4, 1, 1, 10, 35),

(536, 8664, 28, 4, 1, 1, 10, 95),

(537, 7177, 28, 4, 1, 1, 10, 90),

(538, 971, 28, 4, 1, 1, 10, 85),

(539, 5007, 28, 4, 1, 1, 10, 90),

(540, 9515, 28, 4, 1, 1, 10, 95),

(541, 9978, 28, 4, 1, 1, 10, 85),

(542, 6782, 28, 4, 1, 1, 10, 85),

(543, 2828, 28, 4, 1, 1, 10, 80),

(544, 936, 29, 4, 1, 1, 10, 90),

(545, 8664, 29, 4, 1, 1, 10, 95),

(546, 7177, 29, 4, 1, 1, 10, 85),

(547, 971, 29, 4, 1, 1, 10, 90),

(548, 5007, 29, 4, 1, 1, 10, 90),

(549, 9515, 29, 4, 1, 1, 10, 95),

(550, 9978, 29, 4, 1, 1, 10, 90),

(551, 6782, 29, 4, 1, 1, 10, 90),

(552, 2828, 29, 4, 1, 1, 10, 95);

 

 

[Barand]

I give those with same total the same rank, otherwise they are ranked by their position in the sorted (DESC) results

This the bit of the SQL that does it

             , @seq := CASE WHEN yss <> @prev THEN 1
                           ELSE @seq + 1
                           END as seq
             , @rank := CASE WHEN total = @prevtot THEN @rank
                             ELSE @seq
                             END as rank
             , @prevtot := total as total
             , @prev := yss as yss

 

[Ponel]

Is there a way whereby user 7566 and 7057 that takes on rank 3, the next user which is 9208 and 7664 will take on rank 4 and so on so that the number of rank will not be more than the number of student in a class

[Barand]

9208 has 4 students with a higher grade and therefore must be rank"5" and not "4". (If 4 people beat you in a race, you are fifth, even if the 4 winners have the same times)

As the rank is either equal to the previous rank or it is the sequence number, there is no way a rank will be more than the number of students.

[Ponel]

Ok sir. Look at the Sub Position column. and the number of student is in a class is 34 so we have 49th and 47th which is higher than number of student.

 

 

[Barand]

Using your new data I get this (below) for student 1398...

How are you getting the "sub-position"?

[Ponel]

yeah that's for overall position for class. I'm talking of the subject position in the column. With this

SELECT yearid

, semesterid

, regno

, subjectid

, rank

, total

, grade

, comment

FROM (

SELECT yearid

, semesterid

, subjectid

, @seq := CASE WHEN yss <> @prev THEN 1

ELSE @seq + 1

END as seq

, @rank := CASE WHEN total = @prevtot THEN @rank

ELSE @seq

END as rank

, @prevtot := total as total

, @prev := yss as yss

, regno

, armsLevelId -- added

, armsid -- added

FROM (

SELECT yearid

, semesterid

, subjectid

, concat(yearid, semesterid, subjectid) as yss

, regno

, total

, armsLevelId -- added

, armsid -- added

FROM subject_position

ORDER BY yearid, semesterid, subjectid, total DESC

) sorted

JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init

) ranked

JOIN

grade ON total BETWEEN grade.lomark AND grade.himark

WHERE regNo='$_GET[name]' -- A

and armsLevelId='$_SESSION[level_id]' -- D

and armsId='$_SESSION[arms]' -- D

and semesterid='$_SESSION[semester]' -- E

and yearid='$_SESSION[session]' -- D

ORDER BY subjectid

[Barand]

You are calculating the rank out of the whole population, not just the class. You need to include the class (armslevelid) in the groupings

giving

EDIT -- use code tags (<> button)

Edited January 2, 2021 by Barand

[Ponel]

Ok sir I willl check that out

 

 

 

 

 

 

 

 

 

[Ponel]

It worked. Thank you sir

[Mnai]

hello Mr Ponel, i am facing the same problem you faced on "student subject positioning" but my php code has no hope to reach a solution, could you please share your full code to solve this? i am new in coding. Thank you in advance. michaelmnai@gmail.com