# 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
##### 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)
, 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
FROM (
SELECT yearid
, semesterid
, subjectid
, concat(yearid, semesterid, subjectid) as yss
, regno
, total
FROM subject_position
ORDER BY yearid, semesterid, subjectid, total DESC
) sorted
JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
) ranked
JOIN
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 by Ponel
##### 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`
, 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
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;```

##### Share on other sites

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 by Ponel
##### 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       |
+---------------+ ```

##### 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`
, 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
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?

##### 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
##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

Alright sir. I will try it out

##### Share on other sites

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.

##### 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`
, 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
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.

##### Share on other sites

You missed a couple

##### Share on other sites

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

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

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

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

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

##### 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.

##### Share on other sites

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.

##### Share on other sites

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

How are you getting the "sub-position"?

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

, 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

FROM (

SELECT yearid

, semesterid

, subjectid

, concat(yearid, semesterid, subjectid) as yss

, regno

, total

FROM subject_position

ORDER BY yearid, semesterid, subjectid, total DESC

) sorted

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

) ranked

JOIN

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

##### Share on other sites
Posted (edited)

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 by Barand
##### Share on other sites

Ok sir I willl check that out

##### Share on other sites

It worked. Thank you sir

## Join the conversation

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×
×

• #### Activity

• Chat
×
• Create New...