How to find the second largest number

Question

How to find the second largest number

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I need to get 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

asked by anonymous 31.01.2017 / 18:37

2 answers

1

It could look like this:

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

31.01.2017 / 18:44

Error adding 2 values in php [duplicate] Return date with timezone from Brasilia

score 2 · Accepted Answer

You can create two more internal queries that take the two largest records and order them from highest to lowest ( DESC ), so the most external query takes the result and intervenes, ie it orders from lowest to highest ( 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 ( comment ) / p>

<?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'>" : '';