Consider the table marks
marks student
1 a1
1 b1
2 a2
1 b3
30 f1
2 z2
30 gg
i need to select student(s) who scorecd the second largest mark
Second Largest
Moderator: General Moderators
Get the info into an array, in this example, it'll be called '$marks'. Your array will look something like this:
Then do this:
First, it goes through each array value to see which is highest. Fairly straightforward. In the second part, it's seeing which is the highest, but still lower than the number it found. When it finds that, it stores the mark and the name in an array called '$second'. You'll probably want a more elegant way of displaying the information than print_r, but this example is all about function.
Code: Select all
a1 => 1
b1 => 1
a2 => 2
b3 => 1
f1 => 30
z2 => 2
gg => 30Code: Select all
<?php
$high = 0;
foreach ($marks as $num) {
if ($num > $high) {
$high = $num;
}
}
$second['name'] = '';
$second['mark'] = 0;
foreach ($marks as $entry => $num) {
if ($num > $second['mark'] && $num < $high) {
$second['name'] = $entry;
$second['mark'] = $num;
}
}
print_r ($second);
?>Code: Select all
mysql> select * from tst;
+------+------+
| mark | stud |
+------+------+
| 1 | a1 |
| 1 | a2 |
| 30 | a3 |
| 24 | a4 |
| 12 | a5 |
| 6 | a6 |
| 24 | a7 |
+------+------+
7 rows in set (0.00 sec)
mysql> select @sec_larg:=mark from tst order by mark desc limit 1,1;
+-----------------+
| @sec_larg:=mark |
+-----------------+
| 24 |
+-----------------+
1 row in set (0.04 sec)
mysql> select mark,stud from tst where mark=@sec_larg;
+------+------+
| mark | stud |
+------+------+
| 24 | a4 |
| 24 | a7 |
+------+------+
2 rows in set (0.00 sec)
mysql>-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK