How to find the second largest number

Asked

Viewed 377 times

1

I need to search the photo with the second highest salary

<?php

    $host = "localhost";
    $username = "root";
    $password = "";
    $db = "imagem";

    mysql_connect($host,$username,$password) or die("Impossível conectar ao banco."); 

    @mysql_select_db($db) or die("Impossível conectar ao banco"); 

    $img=mysql_query("SELECT * FROM arquivo WHERE sal < (SELECT MAX(sal)FROM arquivo)") or die("Impossível executar a query");

    while($row=mysql_fetch_object($img)) { 
        echo "<img src='getImagem.php?PicNum=$row->codigo' \">"; 
     }          
?>
php mysql sql

  • rray’s worked out!! Thanks guys

2 answers

2


Can create two most internal query takes the two largest records and sorts from largest to smallest (DESC), with this the most external query picks up the result and intervenes it or is ordered from the smallest to the largest (ASC) and returns only one of the two lines.

SELECT sal FROM
      (SELECT t.sal FROM cad as t ORDER BY t.sal DESC LIMIT 2)
ORDER BY sal ASC LIMIT 1

Adapting in php (commenting) stays:

<?php
    $img = mysql_query("SELECT sal FROM (SELECT t.sal FROM cad as t ORDER BY t.sal DESC LIMIT 2) ORDER BY sal ASC LIMIT 1") or die(mysql_error());
    $row = mysql_fetch_object($img);

    echo !empty($row) ? "<img src='getImagem.php?PicNum=$row->codigo'>" : '';

  • however, I can only display on screen the second highest salary

  • @Guilhermefelipesimão vc fez um while? can make two calls from mysql_fetch_asso() the first you discard and the second uses.

  • I couldn’t explain better, I need to get the photo, which contains the second highest salary.

  • <?php $host = "localhost"; $username = "root"; $password = ""; $db = "picture"; mysql_connect($host,$username,$password) or die("Unable to connect to bank."); @mysql_select_db($db) or die("Impossible to connect to bank"); $img=mysql_query("SELECT * FROM file WHERE sal < (SELECT MAX(sal) FROM file)") or die("Impossible to execute query"); while($Row=mysql_fetch_object($img)) { echo "<img src='getImagem.php? Picnum=$Row->code' ">"; } ? > our got too bad to see, tried but n gave tbm

  • @Guilhermefelipesimão I edited my answer, if you need to return one more field do in the two selects.

  • SELECT sal FROM (SELECT t.sal FROM Cad as t ORDER BY t.sal DESC LIMIT 2) ORDER BY sal ASC LIMIT 1 ?

  • @Guillermo.

  • I tried, but it wasn’t enough to show off this time. I put it like this: $img=mysql_query("SELECT * FROM (SELECT t.sal FROM file as t ORDER BY t.sal DESC LIMIT 2) ORDER BY sal ASC LIMIT 1") or die("Unable to execute query");

  • @Guilhermefelipesimão I edited again the answer.

  • 1

    and it worked, thank you

Show 5 more comments

1

It could be so:

SELECT MAX( SAL ) FROM CAD WHERE SAL < ( SELECT MAX( SAL ) FROM CAD )

  • I have many records, so he would display all the lowest of the highest salary.

  • No. Displays only one.

  • he exhibited all =/

  • Hmmmm.. Pure select as is brings only one record, even if there are repeated values. If you are bringing several, I think the problem is elsewhere.

  • If SELECT MAX(salt) FROM Cad brings only one, why SELECT MAX( SAL ) FROM CAD WHERE SAL < ( SELECT MAX( SAL ) FROM CAD ) would bring several?

  • 1

    Demonstrating the idea of the Region: http://sqlfiddle.com/#! 9/596334/2. Note that the result is 60, the second largest, only.

  • I’ll do another post down there, with the code as is, a check .

Show 2 more comments

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.