Page 1 of 1
Second Largest
Posted: Tue Feb 03, 2004 5:19 pm
by vijayanand16
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
Posted: Tue Feb 03, 2004 5:38 pm
by uberpolak
Get the info into an array, in this example, it'll be called '$marks'. Your array will look something like this:
Code: Select all
a1 => 1
b1 => 1
a2 => 2
b3 => 1
f1 => 30
z2 => 2
gg => 30
Then do this:
Code: 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);
?>
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.
Posted: Tue Feb 03, 2004 5:55 pm
by vijayanand16
I need it in SQL Query
Posted: Tue Feb 03, 2004 7:50 pm
by Weirdan
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>
So it require
two queries and one SQL variable or full self x-join, which is extremely slow on huge tables.
Posted: Wed Feb 04, 2004 2:35 am
by timvw
If you are using MySQL you could use LIMIT.
Other SQL dialects have similar stuff.
(I dont know the exact syntax, but that is where the manual is for, i think it is LIMIT number, offset)
SELECT marks
FROM marks
ORDER BY marks DESC
LIMIT 1, 2
Posted: Wed Feb 04, 2004 3:06 am
by malcolmboston
vijayanand16 wrote:I need it in SQL Query
with all due respect, uberpolak has just written out the full code for you, and you provided none (which isnt how the forums are supposed to work) the least you could do is modify the tiny amount of code yourself
jeez