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.