# Student class position generation

Go to solution Solved by Barand,

## Recommended Posts

Hello Guys, my name is Royal and i am working a student result grading system which basically calculates students position from a record of averages from a table. So i have a table which contains student details and there rand total and averages from where i wish to get the individual positions. Here is my table record

```
`class` varchar(255) NOT NULL,
`term` varchar(255) NOT NULL,
`year` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`grand_total` varchar(255) NOT NULL,
`student_average` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `student_grade`
--

(2, 'JS1', 'First Term', '2022', 'JUBILEE IHEANACHO', 'LMS1379467', '706', '235.3333333'),
(5, 'JS1', 'First Term', '2022', 'EMMANUEL OKONJI', 'LMS6941511', '723', '241'),
(6, 'JS1', 'First Term', '2022', 'ROYAL NJOKU', 'LMS2805220', '751', '250.3333333');

--
-- Indexes for dumped tables
--

--
--

--
-- AUTO_INCREMENT for dumped tables
--

--
--
MODIFY `grade_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
COMMIT;```

and below  is my code get the position using Mariadb DENSE_RANK function, yet for every student it keeps returning '1' as position where as the averages varies for the 3 student records

```	<?php

\$student_class_position = mysqli_query(\$conn, "SELECT DENSE_RANK() OVER(ORDER BY grand_total DESC) AS d_rank, class, term, year, name, admission_no, grand_total, student_average FROM student_grade") or die(mysqli_error());

\$class_position = mysqli_fetch_array(\$student_class_position);

\$position = \$class_position['d_rank'];
}
echo \$position;
?>```

Please i need help on this as i have tried even to use RANK() yet same result is still coming up.... also i noticed that if i " SELECT * FROM the table 'student_grade'" and vardumps the data as an array it gives me only the record for the first student record on top.

attached is the screenshot of my table from phpmyadmin

##### Share on other sites

• Solution

Here's how I'd do it without the windowing functions

Note: if you have

```A 98
B 98
C 95```

the A and B a re ranked 1, C is ranked 3.

```\$grade_class = 'JS1';

\$student_class_position = \$conn->prepare( "SELECT rank
FROM (
, @seq := @seq+1 as seq
, @rank := CASE WHEN ord.grand_total = @prev
THEN @rank
ELSE @seq
END as rank
, @prev := ord.grand_total as grand_total
FROM (
, g.grand_total
ORDER BY grand_total DESC
LIMIT 18446744073709551615
) ord
JOIN (SELECT @seq:=0, @rank:=0,@prev:=0) init
) ranked
WHERE class = ?
AND name = ?
AND term = ?
AND year = ?
");

\$student_class_position->execute();
\$student_class_position->bind_result(\$position);
\$student_class_position->fetch();

echo \$position;```

I used a prepared statement so user data is not inserted into the query (SQL injection attack prevention)

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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