Finding 2nd highest salary in employee table - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Finding 2nd highest salary in employee table

vkveenavkveena Posts: 1Member
edited October 2013 in MySQL
Hi,
I wish to know how to find second highest salary in the employee table . Is it right ?

sql>select max(salary)from(select salary from employee where salary not in(select max(salary)from employee))

Please let me know if there is any better way to do it .

- Thanks ,
Veena
Tagged:

Comments

  • HackmanCHackmanC Posts: 441Member
    I think ... this could be Harder, Better, Faster, Stronger (no relation with Daft Punk) ...

    select salary from test order by salary limit 1, 1;

    why ?

    [code]mysql> explain select max(salary) from (select salary from test where salary not in (select max(salary) from test)) a;
    +----+--------------------+------------+-------+---------------+---------+---------+------+------+------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+--------------------+------------+-------+---------------+---------+---------+------+------+------------------------------+
    | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 2 | |
    | 2 | DERIVED | test | index | NULL | idx_sal | 5 | NULL | 3 | Using where; Using index |
    | 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
    +----+--------------------+------------+-------+---------------+---------+---------+------+------+------------------------------+
    3 rows in set (0.00 sec)
    [/code]
    [code]mysql> explain select salary from test order by salary limit 1, 1;
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | test | index | NULL | idx_sal | 5 | NULL | 3 | Using index |
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    1 row in set (0.00 sec)
    [/code]
    Of course you need an index on Salary.

    [red]Good luck![/red]
    [blue]Hackman[/blue]
  • shanphpshanphp Posts: 2Member
    : Hi,
    : I wish to know how to find second highest salary in the employee
    : table . Is it right ?
    :
    : sql>select max(salary)from(select salary from employee where salary
    : not in(select max(salary)from employee))
    :
    : Please let me know if there is any better way to do it .
    :
    : - Thanks ,
    : Veena
    :

    Hi,

    I think this is the best solution for this problem.

    Database:
    --------------
    -- phpMyAdmin SQL Dump
    -- version 2.6.2-pl1
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Sep 01, 2008 at 03:16 PM
    -- Server version: 4.1.11
    -- PHP Version: 5.2.6-dev
    --
    -- Database: `shandb`
    --

    -- --------------------------------------------------------

    --
    -- Table structure for table `emp`
    --

    CREATE TABLE `emp` (
    `eid` int(10) NOT NULL auto_increment,
    `ename` varchar(100) collate utf8_unicode_ci NOT NULL default '',
    `dept` varchar(100) collate utf8_unicode_ci NOT NULL default '',
    `position` varchar(100) collate utf8_unicode_ci NOT NULL default '',
    `salary` double(15,3) NOT NULL default '0.000',
    PRIMARY KEY (`eid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

    --
    -- Dumping data for table `emp`
    --

    INSERT INTO `emp` VALUES (1, 'a', 'php', 'developer', 100.100);
    INSERT INTO `emp` VALUES (2, 'b', 'java', 'developer', 77.000);
    INSERT INTO `emp` VALUES (3, 'c', 'ruby', 'tl', 99.000);
    INSERT INTO `emp` VALUES (4, 'd', 'cf', 'pm', 99.000);

    php script:
    -------------
    <?php

    $conn=mysql_connect("localhost","root","root") or die();
    mysql_select_db("shandb",$conn) or die();

    $sql1 = "select salary from emp group by salary order by salary desc Limit 1,1";

    $sql3="select ename from emp where salary=(".$sql1.")";

    $rs3 = mysql_query($sql3);

    if(mysql_num_rows($rs3) > 0)
    {
    while($row3 = mysql_fetch_array($rs3))
    {
    echo $row3["ename"];
    echo "
    ";

    }
    }
    else
    {
    echo "no records found";
    }

    ?>

    Regards
    ShanmugaSundaram.R
    Software Developer
    PHP/MYSQL
    Chennai
    [email protected]

  • shanphpshanphp Posts: 2Member
    : Hi,
    : I wish to know how to find second highest salary in the employee
    : table . Is it right ?
    :
    : sql>select max(salary)from(select salary from employee where salary
    : not in(select max(salary)from employee))
    :
    : Please let me know if there is any better way to do it .
    :
    : - Thanks ,
    : Veena
    :

    Hi,

    I think this is the best solution for this problem.

    Database:
    --------------
    -- phpMyAdmin SQL Dump
    -- version 2.6.2-pl1
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Sep 01, 2008 at 03:16 PM
    -- Server version: 4.1.11
    -- PHP Version: 5.2.6-dev
    --
    -- Database: `shandb`
    --

    -- --------------------------------------------------------

    --
    -- Table structure for table `emp`
    --

    CREATE TABLE `emp` (
    `eid` int(10) NOT NULL auto_increment,
    `ename` varchar(100) collate utf8_unicode_ci NOT NULL default '',
    `dept` varchar(100) collate utf8_unicode_ci NOT NULL default '',
    `position` varchar(100) collate utf8_unicode_ci NOT NULL default '',
    `salary` double(15,3) NOT NULL default '0.000',
    PRIMARY KEY (`eid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

    --
    -- Dumping data for table `emp`
    --

    INSERT INTO `emp` VALUES (1, 'a', 'php', 'developer', 100.100);
    INSERT INTO `emp` VALUES (2, 'b', 'java', 'developer', 77.000);
    INSERT INTO `emp` VALUES (3, 'c', 'ruby', 'tl', 99.000);
    INSERT INTO `emp` VALUES (4, 'd', 'cf', 'pm', 99.000);

    php script:
    -------------
    <?php

    $conn=mysql_connect("localhost","root","root") or die();
    mysql_select_db("shandb",$conn) or die();

    $sql1 = "select salary from emp group by salary order by salary desc Limit 1,1";

    $sql3="select ename from emp where salary=(".$sql1.")";

    $rs3 = mysql_query($sql3);

    if(mysql_num_rows($rs3) > 0)
    {
    while($row3 = mysql_fetch_array($rs3))
    {
    echo $row3["ename"];
    echo "
    ";

    }
    }
    else
    {
    echo "no records found";
    }

    ?>

    Regards
    ShanmugaSundaram.R
    Software Developer
    PHP/MYSQL
    Chennai
    [email protected]

  • nipanipa Posts: 1Member
    OR U CAN EASILY FATCH SECOND HIGHEST SALARY OF EMPLOYEES BY A SINGLE QUWEY
    SELECT *
    FROM emp
    WHERE salary = (
    SELECT salary
    FROM emp
    GROUP BY salary
    ORDER BY salary DESC
    LIMIT 1 , 1 )
  • prathvirajprathviraj Posts: 1Member
    Worked for me. Thank You
Sign In or Register to comment.