Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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
    shanmugasundaram.webdeveloper@gmail.com

  • 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
    shanmugasundaram.webdeveloper@gmail.com

  • 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.