Hello Guys…
There is a Question for you.
How to find third or nth maximum salary from salary table(EmpID,EmpName,EmpSalary) in Optimized way?
Thanks in Advance. 
1 Like
Build array of salaries in descending order and grab third and ninth.
<?php
$host = "localhost";
//Database user name.
$login = "";
//Database Password.
$dbpass = "";
//Database name.
$dbname = "";
$PDO = new PDO("mysql:host=localhost;dbname=$dbname", "$login", "$dbpass");
$Salaries = array();
$query = $PDO->prepare("SELECT EmpID,EmpName,EmpSalary FROM salary ORDER BY EmpSalary DESC");
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
$Salaries[$row['EmpID']]['Salary'] = $row['EmpSalary'];
$Salaries[$row['EmpID']]['EmpName'] = $row['EmpName'];
}
$emp = array();
$cnt =1;
foreach($Salaries as $id => $Salary){
if($cnt==3 || $cnt==9){
$emp[$id] = $Salary['Salary'];
}
$cnt++;
}
echo "And the winner is<br />";
foreach($emp as $id => $Salary){
$EmpName = $Salaries[$id]['EmpName'];
echo "Name: " . $EmpName . " Salary: $" . $Salary . "<br />";
}
?>
That code isn’t very well optimized.
Why build an array of all the results when you only want two of them.
I am not going to claim that the following is the most efficient way to do it as it is based the above version with just the most obvious change being made to improve the efficiency.
<?php
$host = "localhost";
//Database user name.
$login = "";
//Database Password.
$dbpass = "";
//Database name.
$dbname = "";
$PDO = new PDO("mysql:host=localhost;dbname=$dbname", "$login", "$dbpass");
$cnt =1;
echo "And the winner is<br />";
$query = $PDO->prepare("SELECT EmpID,EmpName,EmpSalary FROM salary ORDER BY EmpSalary DESC");
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
if($cnt==3 || $cnt==9){
echo "Name: " . $row['EmpName'] . " Salary: $" . $row['EmpSalary'] . "<br />"; ;
}
$cnt++;
}
?>
All true.
You did post some code as a starting point though.
Now someone else will come along and see something else that stands out as inefficient and improve it further.
Yea, isn’t that how it usually goes…
I guess it comes from the fact that I hardly ever run any queries in html, but rather build output in some way before hand, so I was thinking along the lines of having just the final array to loop through as output.
Thank you sir but This code is so big.
Can we make it done in a short code.
Do you want the “third or ninth”, or “third and ninth”? The difference important.
Hello Sir,
I want 3rd and 9th Highest Salary Form MySQL using php.
Personally, I wouldnt use PHP for 90% of this work.
SELECT EmpID,EmpName,EmpSalary FROM salary ORDER BY EmpSalary DESC
=>
SELECT EmpID,EmpName,EmpSalary FROM salary ORDER BY EmpSalary DESC LIMIT 2,7
So now the query only selects the third through the ninth results.
$vals = $query->fetchAll();
$third = array_shift($vals);
$ninth = array_pop($vals);
unset $vals;
And if you’re just going for the Nth, LIMIT N-1,1. then you get back a single row result which is the one you want.
Very cool StarLion. I’ve never seen LIMIT used that way. Something so basic I’ve missed.
SELECT
e1.EmpSalary
FROM
salary e1
WHERE
(n - 1) = (
SELECT
COUNT(DISTINCT e2.EmpSalary)
FROM
salary e2
WHERE
e2.EmpSalary > e1.EmpSalary)
So converting that to retrieve the 3rd and 9th as originally requested gives
SELECT e1.EmpSalary FROM salary e1
WHERE ( SELECT COUNT(DISTINCT e2.EmpSalary) FROM salary e2
WHERE e2.EmpSalary > e1.EmpSalary) IN(2,8)
It is always preferable to avoid using LIMIT if possible so as to make the SQL more portable - just in case you ever move away from mySQL>
Depends. If your code is for a personal site in which you know you’ll never move away from MySQL, ‘portable’ may not be preferable over running subqueries. (I… would ASSUME a LIMIT is faster than a subquery. I could be wrong.)
I’m not 100% positive but I’m pretty sure that the correlated subquery is more scaleable than limit. I guess we need rudy to confirm.
No one has yet posted a way to get the both the 3rd and 9th entries and no others using a single query using LIMIT.
The subquery version can be easily extended to include any specific list of entries by specifying them in the IN list within the subquery.
Years ago I used to have a similar mindset, but as our customers started to need large and larger server clusters to cope with traffic, I changed to use any MySQL feature available to improve the query speed and not worry about if the client would change to PostgreSQL or Oracle in the future, as the truth is that the chance for that to happen is less than slim. If you go with commercial MySQL versions like Percona, MySQL scale very well and when you reach the point where you cant use MySQL any more you need to move to NoSQL anyway (which will require you to rethink the structure).
I am not saying that there never will be a case where a change like this would happen, but in my experience the optimizing you are able to get out of the queries is worth the drawback of not being able to switch right away.
A sub query is normally never the best solution if we look at scalability. In this case the limit would be faster, but of course means that the result would need to be looped over on the PHP level.
Note. I assume there is an index on the salary column, which there should be no matter if the sub query or the limit is used.
It’s Also Work’s.
<?php
mysql_connect("localhost","root","");
mysql_select_db("test");
$cnt =1;
$query = mysql_query("SELECT * FROM test ORDER BY amount DESC");
while($row = mysql_fetch_array($query)){
if($cnt==3 or $cnt==7){
echo "Name: " . $row['name'] . "<br>Salary: " . $row['amount'] . "<br />"; ;
}
$cnt++;
}
?>