Second Largest

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
vijayanand16
Forum Newbie
Posts: 12
Joined: Fri Jan 30, 2004 4:57 pm

Second Largest

Post 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
User avatar
uberpolak
Forum Contributor
Posts: 261
Joined: Thu Jan 02, 2003 10:37 am
Location: Next to the bar

Post 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.
vijayanand16
Forum Newbie
Posts: 12
Joined: Fri Jan 30, 2004 4:57 pm

Post by vijayanand16 »

I need it in SQL Query
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post 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
Post Reply