Jump to content

Student subject Positioning based on score using php and mysql database


Recommended Posts

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 by Ponel
Link to post
Share on other sites

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

Capture___.PNG.b0f52a3532d70bc7094a16aa52c6613a.PNG

for user 6073

image.png

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

 

Any clue on this sir

 

Edited by Ponel
Link to post
Share on other sites

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;

 

Link to post
Share on other sites

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       |
                          +---------------+ 

 

Link to post
Share on other sites

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?

 

Link to post
Share on other sites
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 by Barand
Link to post
Share on other sites

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;

 

---.PNG.79e03e22bd379e7d048ba9ec8156946c.PNG

1505422657_---.PNG.59a11970d24b4d4c6b4f659f2b846903.PNG

 

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. 

Link to post
Share on other sites

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);

 

ties.PNG.5382583d43969f324f5b683c3b9219d3.PNG

 

Link to post
Share on other sites

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

image.png.590597836b658fa057ff61e143e57cad.png

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

 

Link to post
Share on other sites

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

ties.PNG.fbb4740abf6cab58ea6a992ea6d098cd.PNG

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

Link to post
Share on other sites

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

image.png.4c90a047564bcaf0af485b541473e0a1.png

giving

image.png.f0beb7df1403537b831c6e4a46728a11.png

EDIT -- use code tags (<> button)

Edited by Barand
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.