Home > Database, SQL Server > How do you find the Second highest Salary?

How do you find the Second highest Salary?

This is the most common question asked in Interviews.

EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?

Answer:

We can write a sub-query to achieve the result

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.

About these ads
Categories: Database, SQL Server
  1. NABEEL
    November 24, 2006 at 5:40 am

    I really liked this post. :-)

  2. November 28, 2006 at 10:02 am

    Excellent way to find that!

  3. Anurag Kumar
    December 4, 2006 at 10:56 am

    SELECT TOP 1 stud_name,stud_marks
    FROM (SELECT TOP 2 stud_name, stud_marks FROM student ORDER BY stud_marks DESC) a
    ORDER BY stud_marks ASC

    • Dhananjay
      September 9, 2011 at 6:50 am

      Sir ,
      Why you create object ‘a’ in query

    • SAMRAT
      September 11, 2011 at 6:03 am

      AWESOME!

    • Dip
      February 5, 2012 at 7:49 am

      we should also use distinct in nested select to omit duplicate values

  4. kalyan gupta
    January 3, 2007 at 11:41 am

    excellent

  5. kapil
    January 9, 2007 at 5:39 am

    Please refer to following Sql Query it will work:
    select top 1 * from salary where salary in(select top 2 salary from salary order by desc)

  6. Pramendra Chaudhary
    January 16, 2007 at 4:29 pm

    Kapil,
    The post submitted by you will not work dear,
    Reason: If your salar column having same value multiple time then this query will not show correct one,try this)

    the post submitted by employee is ok.

  7. Hariraj
    January 30, 2007 at 12:27 am

    Firstly I hate using the IN clause…. anyways the following query will work for…

    a. if there are multiple Highest salary….
    b. will list out names of all the second highest salary ….

    select name, salary from emp where salary =
    (select max(salary) from emp where salary

  8. Rubinaalexmary
    March 1, 2007 at 10:17 am

    Select Top 1 salary from emp where salary
    Not IN(select Top 1 salary from emp order by salary Desc)order by salary Desc

    • jagadish
      February 20, 2012 at 4:18 am

      Correct query .. thanks dude …

  9. Ashutosh das
    March 15, 2007 at 9:24 am

    select max(sal) from employee where sal

  10. chanti
    April 24, 2007 at 11:43 am

    select max(sal) from emp
    where sal

  11. gagan
    May 24, 2007 at 8:08 am

    select max(emp_sal) from salary where emp_sal not in ( select top(1) emp_sal
    from salary order by emp_sal desc)

  12. ravi
    May 30, 2007 at 8:01 am

    Hai alll

    i want query for 3rd highest salary in a table

    please guide me my mail id

    marni.ravikiran@hotmail.com

    thank u
    ravi

    • Sumi
      July 6, 2010 at 7:02 am

      select max(salary) from emp_salary
      where salary not in (select top 2 salary from emp_salary order by salary desc)

    • ravindra
      May 13, 2011 at 10:08 am

      select *from emp e where 2=(select count(distinct sal) from emp m where m.sal>e.sal)

      this will work perfectly try it.

      here put ‘0’ — 1st highest sal
      ‘1’—2nd highest sal…..etc

      • Joe Kidd
        May 31, 2011 at 5:50 pm

        Hi Ravindra,

        This was the best of the lot.
        It also works even in SQL 2000.

        I just reordered your query as an SP

        –QUERY FOR Nth MAXIMUM SALARY RECORD
        –Rank 0 is first rank, 1 is second rank, 2 is third rank
        CREATE PROCEDURE NthMaxRecord (@position int)
        AS
        SELECT * FROM EMP E WHERE (@position – 1) = (SELECT COUNT(DISTINCT BASIC) FROM EMP M WHERE M.BASIC>E.BASIC)

        –EXECUTING THE STORED PROCEDURE (HERE FOR 3rd RANK)
        EXEC NthMaxRecord 3

        Thanks once again Ravindra

        – Joe

  13. kuber
    May 31, 2007 at 5:04 am

    Hi ALL,
    can any one tell me to find out the “nth highest salary”?
    thanx in advance.
    KMR

    • ravindra
      May 13, 2011 at 10:46 am

      select *from emp e where 2=(select count(distinct sal) from emp m where m.sal>e.sal)

      this will work perfectly try it.

      here put ’0′ — 1st highest sal
      ’1′—2nd highest sal…..etc

      • AjayKumar
        June 3, 2013 at 2:58 pm

        HI ravi,your Query working correctly. can you explain how it works?

  14. Kiran Reddy
    June 16, 2007 at 11:34 am

    Hi Ravi try 4 this u may get…

    SELECT [Employee_ID],
    [number],
    [name],
    [salary]
    FROM [Employee]
    WHERE [salary] = (
    SELECT MIN( [salary] )
    FROM (
    SELECT DISTINCT TOP 3 [salary]
    FROM [Employee]
    ORDER BY [salary] DESC
    ) [FourHighestDistinctSalaries]
    )

    • March 24, 2011 at 7:12 am

      can you tell me how to get 3rd highest salary from the table emp

      • hrudananda Biswal
        April 23, 2011 at 12:54 pm

        SELECT TOP 1 salary
        FROM (
        SELECT DISTINCT TOP 3 salary
        FROM employee
        ORDER BY salary DESC) a
        ORDER BY salary

  15. manoj
    June 17, 2007 at 2:01 pm

    How do you find the third highest Salary?

  16. June 21, 2007 at 9:28 am

    i have a complete post related to this, in this article i have posted quries for both SQl Server 2000 and 2005 http://hemantg.blogspot.com/2007/06/retrieving-nth-salary-in-sql-server.html

  17. anilG
    July 3, 2007 at 2:22 am

    Thanks for this better solution

  18. ravi
    July 5, 2007 at 7:30 am

    If you want employee greatest salary ( 1st or 2nd or 3rd …. any thing ,in place of “N” in query, place the number)

    SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
    FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))

  19. Ashwani
    July 5, 2007 at 9:35 am

    top 5th max salary of a employee in a employee table

    SELECT * FROM employee e1 WHERE (5 = (SELECT COUNT(DISTINCT (e2.emp_salary))
    FROM employee e2 WHERE e2.emp_salary >= e1.emp_salary))

  20. Amol Pawar
    July 16, 2007 at 12:49 pm

    SELECT max(Salary)FROM Emp WHERE Salary <select MAX(Salary) from Emp

  21. pankaj
    July 19, 2007 at 6:02 pm

    select min(sal) from emp where (sal) IN (select top2 sal from emp);

  22. radhika
    July 25, 2007 at 3:49 pm

    SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
    FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))

    can any one explain this query whats the e1 & e2 tables

  23. paragalathan
    July 26, 2007 at 9:57 am

    How do you find the 4th highest Salary in sql server without using subquery? pls reply

    • arunesh
      May 4, 2010 at 11:07 am

      select min(sal) from(select sal from tablename order by sal desc)
      where rownum<=4;

      • Khadim Hussain
        March 15, 2011 at 11:25 am

        select ename,sal from(select ename,sal from emp
        order by sal desc
        where rownum<=3

  24. pragalathan.p
    July 26, 2007 at 9:58 am

    pls reply.
    How do you find the 4th highest Salary in sql server without using subquery?

    • abhishek
      April 22, 2010 at 6:00 am

      Select Top 1 sal from employee where sal
      Not IN(select Top 3 sal from employee order by sal Desc)order by sal Desc

    • shahbaz
      July 5, 2011 at 3:19 pm

      How do you find the 6th highest Salary in sql server & oracle 10g without using subquery?

  25. Mahesh
    July 30, 2007 at 7:08 pm

    3rd highest salary in a table

    Select Top 1 salary from empsal where salary
    Not IN(select Top 2 salary from empsal order by salary Desc)order by salary Desc

    • June 17, 2010 at 12:56 pm

      i wanted to know the second highest salary
      and second highest salary in two table

  26. Naresh
    August 18, 2007 at 5:33 am

    TO FIND THE Nth HIGHEST SALARY

    SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM=5;

  27. Naresh
    August 18, 2007 at 5:39 am

    SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM=N;

  28. Naresh
    August 18, 2007 at 5:46 am

    I WANT TO CONNECT THE MULTIPLE USERS IN ORACLE1Og
    WHTS THE PROCESS

  29. October 11, 2007 at 9:25 am

    How to find highest 2 salary from a table

    • March 24, 2011 at 10:26 am

      select Distinct Top 2 Salary from EMP order by Salary desc

  30. NISAR
    October 16, 2007 at 2:55 pm

    Thanks Dear..

    It is really helpful to complete my requirement…

  31. October 29, 2007 at 2:09 am

    this is good site for us for search any question for sql as well as orecle thenks .

  32. November 1, 2007 at 10:05 am

    SELECT * FROM temp_emp T1 WHERE
    4 = (SELECT COUNT(DISTINCT(T2.salary)) FROM temp_emp T2 WHERE T2.salary >= T1.salary);

  33. November 24, 2007 at 12:58 pm

    this site is good.
    how to join two different tables.

  34. Sapna
    November 26, 2007 at 2:33 pm

    Nice one. This post contains all the possible options to write the query.

  35. Praveen
    January 11, 2008 at 4:15 am

    Too good. Thanks everyone for sharing the knowledge.

  36. Nitin A. Arbale
    January 18, 2008 at 9:00 am

    ravi’s post rocks.

    Thanks Alot

  37. Ram
    January 23, 2008 at 9:44 am

    really the query helps…

  38. January 23, 2008 at 11:13 am

    grt man………..

    • March 24, 2011 at 10:22 am

      see this website

  39. viji
    January 24, 2008 at 9:02 am

    how two find the second maximum number by using sqlserver without sub query

  40. February 8, 2008 at 12:58 pm

    select min(sal) from emp where sal in(select distinct top 2 sal from emp order by sal desc)

  41. chaitanya
    February 12, 2008 at 6:59 am

    1. select max(esal) from emp where esal not in(select max(esal) from emp

    2.select esal from emp e1 where 2=(select count(distinct esal) from emp e2 where e2.esal>=e1.esal)

    3.select top2 * from emp where esal not in(select max(esal) from emp)

  42. Anil Verma
    April 30, 2008 at 2:34 pm

    select top 1 * from salary where salary in(select top 2 salary from salary order by desc)
    is not working

  43. Srinivas
    May 14, 2008 at 9:34 am

    select distinct sal from employee t
    where 3 = (select count(distinct sal)
    from employee
    where t.sal<=sal)

  44. Abhishek
    July 22, 2008 at 9:02 am

    I v have more than 10 equal and highest salary employee

  45. rabi
    July 25, 2008 at 12:51 pm

    Select Top 1 salary from salary where salary
    Not IN(select Top 1 salary from salary order by salary Desc)order by salary Desc

    select max(salary) from salary where salary not in ( select Top 1 salary from salary order by salary desc)

  46. August 20, 2008 at 1:59 pm

    select trk_id from csl_trk a where 3=(select count (distinct(b.trk_id))from csl_trk b where a.trk_id<=b.trk_id);

  47. bhavani
    November 14, 2008 at 5:41 am

    how to find second highest salary in postgreSQL.

  48. Magesh
    November 21, 2008 at 12:32 pm

    select distinct(Salary),* from Employee A where 3=(select count(distinct(Salary)) from Employee b where b.Salary <= a.Salary)

  49. sujan
    November 27, 2008 at 10:58 am

    nice

  50. sujan
    November 27, 2008 at 10:59 am

    only one

  51. Surendra Gurjar
    December 31, 2008 at 11:05 am

    select top 1 salary from employee where salary<(select max(salary) from
    employee) order by salary desc

    it will work for all condition for ms sql server 2005
    to find the second highest salary ……….

    SURENDRA GURJAR

  52. Pallavi Attarde
    March 16, 2009 at 12:37 pm

    Here N is any number(you just put that number here and find that number of highest salary)
    This query takes data from two tables
    1.EMPLOYEE
    2.SALARY_DETAILS

    SELECT TOP 1 * FROM (SELECT TOP N * FROM (SELECT EMPLOYEE.FNAME,SALARY_DETAILS.SALARY FROM EMPLOYEE INNER JOIN SALARY_DETAILS ON EMPLOYEE.ID = SALARY_DETAILS.ID) AS D ORDER BY SALARY DESC) AS P ORDER BY SALARY

  53. Mahfooz
    April 17, 2009 at 7:51 am

    I want to get the employee name as well for the second highest salary getter, if I do

    SELECT ENAME, MAX(SAL)
    FROM EMP
    WHERE SAL < (SELECT MAX(SAL) FROM EMP)

    or

    SELECT ENAME, MAX(SAL) AS SAL
    FROM EMP
    WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP)

    then an error message says
    “Column ‘emp.ENAME’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”
    Plz provide a solution
    I donot want to use the TOP Function
    Regards
    Mahfooz

    • Rajesh potnuru
      May 4, 2011 at 12:05 pm

      select ename,sal from emp where sal=(
      select max(sal)
      from emp
      where sal<(select max(sal) from emp) )

      Thanks,
      -Rajesh

  54. Mahesh
    April 17, 2009 at 1:16 pm

    How to find the second max salary for each deportments for the below examples.
    Table Name is EMP:

    Name Age Sal Dept
    ——————————————————
    AA 15 1000 10

    BB 20 3500 20

    CC 18 3000 10

    DD 21 2500 10

    EE 19 3000 20

    Expected Output is,

    Sal Dept
    —————————-
    3000 10

    3500 20

    Please share your valuable information.

  55. Uneeb
    April 21, 2009 at 11:22 am

    That’s a bit tricky ……. I just lost 10 marks due to this ……….. ::(

  56. April 22, 2009 at 10:49 am

    How to find second highest value of a column in a table? – SQL …

    Declare @temp int

    set @temp = 2

    SELECT MAX(SALARY) from EMPLOYEE_PAY_TBL WHERE SALARY NOT IN ( SELECT TOP (@temp – 1) SALARY FROM EMPLOYEE_PAY_TBL ORDER BY SALARY DESC)

  57. Sandesh
    June 5, 2009 at 11:45 am

    Gud Site

  58. Subbarao
    June 28, 2009 at 7:39 pm

    Please mention Oracle Version for usage of TOP keyword.

  59. Darshana
    June 30, 2009 at 10:44 am

    Select emp_sal from salary where emp_sal not IN (Select max(emp_sal) from salary) ORDER BY emp_sal DESC LIMIT 0 , 1

  60. July 6, 2009 at 12:27 pm

    Excellent code

  61. srinivas
    August 24, 2009 at 7:23 am

    how 2 find the top 5 salaries of employees in the employee table.

  62. srinivas
    August 24, 2009 at 7:25 am

    how 2 enter one system from another system when they r connected in network,and how 2 trace the second system password.

  63. VIKASH
    October 15, 2009 at 9:37 am

    select * from employee orderby salary DESC
    Limit 1,1

  64. Dhaval
    October 26, 2009 at 10:33 am

    My answer for same questions

    SELECT SALARY FROM EMPLOYEE WHERE Order by SALARY Desc limit 1,1.

  65. Raghavendra
    November 18, 2009 at 5:25 am

    select max(sal) from emp where sal not in (select top 1 sal from emp order by sal desc)

  66. Saurabh Goyal
    November 25, 2009 at 3:22 am

    select a.sal
    from emp a
    where 2 = (
    select count(distinct b.sal)
    from emp b
    where b.sal >= a.sal
    )
    order by a.sal desc;

  67. Saurabh Goyal
    November 25, 2009 at 3:24 am

    select max(sal)
    from emp
    where sal not in (select max(sal) from emp)

  68. suman
    November 25, 2009 at 10:46 am

    very gud ans

  69. Rajesh Pant
    December 9, 2009 at 10:03 am

    select min(PaymentAmount) from
    (select distinct top 7 t1.PaymentAmount from dbo.MonthlyPayments t1 order by t1.PaymentAmount desc) a

  70. malli
    December 25, 2009 at 2:09 am

    hi, this is very nice
    it was very usefull to me
    thanks

  71. sachin
    January 5, 2010 at 9:29 am

    select max(sal) from EMP
    where sal< (select max(sal) from EMP)

  72. sachin
    January 5, 2010 at 9:31 am

    Second minimum sal in EMP table

    select min(sal) from EMP
    where sal > (select min(sal) from EMP)

  73. amita
    January 13, 2010 at 5:14 am

    SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
    FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))

    please explain this query

  74. Usha
    January 23, 2010 at 10:10 am

    I tried this query.. it works fine…

    SELECT EmpName, Salary
    from
    (
    SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS ‘Salaries’
    FROM #Employees
    ) emp
    WHERE Salaries = n

    n may be 2,3….

  75. January 29, 2010 at 6:40 am

    Please send how to code to find the highest record from a field while the number is added with any string.
    for example “BIS001,BIS002,BIS009,BIS010,BIS003″…i need ans hear is BIS010

  76. vishal
    March 2, 2010 at 6:51 pm

    we can use this query to display all the records regardinh second highest salary

    select * from emp
    where sal=(select max(sal) from emp where sal<(select max(sal) from emp));

    use this it will work

  77. santosh soni
    April 5, 2010 at 8:53 am

    SELECT MAX(sal) FROM emp WHERE sal < (SELECT MAX(sal) FROM emp)

    • shivaji
      July 27, 2011 at 6:02 pm

      this was super….. my frd… thanks for this

  78. Abdullah
    May 3, 2010 at 8:18 am

    thank u for ur positive response
    i will contact when i have another problem
    Ok have a good

  79. amit singh
    August 10, 2010 at 5:26 am

    Empsal table data
    700
    500
    100
    900
    400
    200
    600
    750
    query to find second highest salary from table Empsal

    mysql>select distinct(max(e.salary)) from Empsal e
    >where e.salary in(
    >select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));

    Output=750

  80. amit singh
    August 10, 2010 at 5:27 am

    forget nthe book logic
    query to find third highest salary from table Empsal

    mysql>select distinct(max(e.salary)) from Empsal e
    >where e.salary in(
    >select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
    >where e1.salary IN(
    >select e2.salary from Empsal e2 where
    >e2.salary<(select max(salary) from Empsal))));
    Output=700
    amitsing2008@gmail.com(amy is back)

  81. August 31, 2010 at 2:11 pm

    hi I find out dob january releate all emloyee

    and my column filed is id,name dob

    how to find dob of only january employee find out whatever any years .

    so ,plz help me sir

    select name, dob from employee where dob= ‘0000-01-00 ‘

    this type we try to find out .

  82. chithra
    October 5, 2010 at 9:47 am

    SELECT MAX(sal) AS Expr1 FROM emp WHERE (sal NOT IN
    (SELECT MAX(sal) AS Expr1 FROM emp AS expr2))

  83. October 15, 2010 at 5:02 pm

    Excellent Answer

  84. pritesh
    January 22, 2011 at 6:13 am

    how t print this
    1
    2
    three
    .
    .
    10
    11
    ..1three
    …2three
    three three

    • pritesh
      January 22, 2011 at 6:14 am

      simpel

  85. February 17, 2011 at 8:19 am

    Here you will get second highest and lowest value from the record table

    Second Highest:

    SELECT * FROM empsalry s WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary s1.salary);

    nth lowest salary

    SELECT * FROM empsalry s WHERE n-1 =
    (SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary > s1.salary);

  86. February 17, 2011 at 8:21 am

    sorry for previous post correct is here:

    second highest:

    SELECT * FROM empsalry s WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary s1.salary);

    lowest salary

    SELECT * FROM empsalry s WHERE n-1 =
    (SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary > s1.salary);

  87. February 17, 2011 at 8:24 am

    Here something is wrong in this blog i am trying to put symbol, but not able to show it on post. I don’t know what is the problem so plese s.salary < s1.salary in first query

  88. February 21, 2011 at 7:45 am

    Hi Friend’s i am Gaurav Badhani, and i think this querry will work for 2nd highest salary if u want try this and for more help give me a querry in badhani_gaurav@yahoo.com

    SELECT max(Salary)FROM Emp WHERE Salary <select MAX(Salary) from Emp

  89. bahadar
    April 5, 2011 at 6:15 am

    thats greate and simple way. i thought it would be very complex.

    thanx

  90. arsath ayoob f
    April 6, 2011 at 6:01 pm

    Thank u so much friend ….

  91. Sunil Raj Sonkar
    April 16, 2011 at 1:22 pm

    please give me a suggestion for second highest salary in sql

    • Sunil Raj Sonkar
      April 16, 2011 at 1:29 pm

      Hi all,
      Find the ‘ORDER BY’ and ‘GROUP BY’ in sql for me.

      • Neha Rana
        June 29, 2011 at 5:17 am

        SELECT *
        FROM `table name`
        ORDER BY `salary` DESC
        LIMIT 1 , 1

  92. Sunil Raj Sonkar
    April 16, 2011 at 1:35 pm

    Hi all,
    find out the ‘magic methods’.

  93. Rakesh
    April 28, 2011 at 6:00 pm

    Why are you people making it complicated… I will give one easy query to find n highest salary…

    select min(salary) from (select distinct top n salary from emp order by salary desc)

    where n = 2,3,… (2=second highest salary, 3=third highest salary,…)

    • Rakesh
      April 28, 2011 at 6:06 pm

      small change…

      select min(salary) from emp where salary in (select distinct top n salary from emp order by salary desc)

  94. Rakesh
    April 28, 2011 at 6:07 pm

    select min(salary) from emp where salary in (select distinct top n salary from emp order by salary desc)

  95. Anbu
    June 3, 2011 at 12:29 pm

    thanks a lot

  96. Amit
    June 13, 2011 at 12:48 pm

    Really neat and simple solution…
    Thanks..

  97. Kushal
    June 23, 2011 at 11:36 am

    Nice!

    • Neha
      June 29, 2011 at 5:09 am

      SELECT *
      FROM `test`
      ORDER BY `last_name` DESC
      LIMIT 1 , 1

      GO for it

  98. Neha Rana
    June 29, 2011 at 5:12 am

    SELECT *
    FROM `table name`
    ORDER BY `salary` DESC
    LIMIT 1 , 1

    GO for it

  99. July 5, 2011 at 3:21 pm

    +

  100. July 30, 2011 at 1:56 pm

    how can we get last five record from table

  101. August 9, 2011 at 11:21 am

    come on baby

  102. umar
    August 29, 2011 at 3:21 pm

    It will return top second highest salary:

    SELECT MAX(SALARY) FROM
    (SELECT TOP (2) emp.SALARY FROM EMPLOYEE emp)Q1

  103. KEROMERO
    October 27, 2011 at 3:43 pm

    SELECT TOP 2
    MAX SALARY AS [MAXIMUM SALARIES]
    FROM YOUR_TABLE
    ORDER BY SALARY DESC

  104. Jignesh
    November 2, 2011 at 7:09 pm

    The following query will work for nth highest salary.

    SELECT EName,salary FROM Employee A
    Where n = (select Count (distinct(salary)) From Employee B
    WHERE B.salary>=a.salary)

    In the above query, replace n with the number you want.

  105. Muhammad sumeer
    November 17, 2011 at 7:32 pm

    xcellent, realy xcellent.

  106. dhanunjay
    December 4, 2011 at 12:01 pm

    SELECT MIN(Sal) FROM TableName
    WHERE Sal IN
    (SELECT TOP 2 Sal FROM TableName ORDER BY Sal DESC)

  107. December 19, 2011 at 3:40 pm

    SELECT SALARY
    FROM EMPLOYEE
    ORDER BY SALARY DESC
    LIMIT 1 , 1

    limit 1.1 means second result to second result . minimum limit is zero. for second result limit 1,1 and remember one thing descending order is used

    • Epsita das
      December 20, 2011 at 11:51 am

      this one is good

  108. January 15, 2012 at 1:49 pm

    ya this one

  109. January 25, 2012 at 8:56 am

    Simple Solution>>>>>>>>>>
    select & from table t1 where 2=(select count(distinct age) from table t2 where t2.age>t1.age);

    • January 25, 2012 at 8:57 am

      select * from table t1 where 2=(select count(distinct age) from table t2 where t2.age>t1.age);

  110. January 28, 2012 at 6:23 am

    SELECT MAX(SALARY) FROM mgm WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM mgm) From this we can only find second salary.. but for others like fifth forth tenth etc salaries this method is not good I thing. Check with this query. May this will help you.

    CREATE TABLE mgm
    (name varchar(20),no int,addr varchar(20),dept varchar(20),country varchar(20),salary int)

    INSERT INTO mgm values(‘af’,3,’egh’,’erg’,’sder’,656)
    INSERT INTO mgm values(‘fgdg’,2,’sg’,’sg’,’sderty’,567)
    INSERT INTO mgm values(‘afdg’,4,’sgfdsg’,’fdgh’,’ety’,678)
    INSERT INTO mgm values(‘dfghdh’,5,’sgf’,’tey’,’reyt’,4565)
    INSERT INTO mgm values(‘dgh’,6,’fr’,’rttr’,’sdhyj’,5467)
    INSERT INTO mgm values(‘dshgf’,7,’ttyhyh’,’hhh’,’sd’,4767)

    select name,no,addr,dept,country,salary from
    (select dense_rank() over (order by salary desc) as ranking,name,no,addr,dept,country,salary from mgm
    )a
    where a.ranking in (1,2,3)

  111. February 12, 2012 at 10:24 am

    price;
    ?>

  112. February 12, 2012 at 10:26 am

    select price FROM sub_price ORDER BY price desc LIMIT 1, 2

  113. Arpit Parikh
    March 6, 2012 at 1:08 pm

    SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)……will give 2nd highest salary

    how to get the nth highest salary????

  114. Arpit Parikh
    March 6, 2012 at 1:09 pm

    SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)….will give the 2nd highest salary

    how to get the nth highest salary???????

  115. Anudeep
    March 21, 2012 at 9:21 am

    gud evng all ,,,
    when i use TOP in query,, then show ,,” Invalid Identifier ”
    pls tell me the reason

  116. aaron
    March 30, 2012 at 6:16 am

    Select salary from Employee order by salary desc limit 1,1
    For retrieving the second highest salary from the Employee table.

  117. gurpreet kaur
    May 22, 2012 at 12:06 pm

    TO SELECT SECOND MAX SALARY

    select max(salary)from table_name where salary<(select max(salary) from table_name);

  118. Sovan Dey
    July 6, 2012 at 2:16 pm

    SELECT DISTINCT SAL FROM EMPLOYEE ORDER BY SAL ASC LIMIT 1 , 1

    OR

    SELECT SAL FROM EMPLOYEE GROUP BY (SAL) ORDER BY SAL ASC LIMIT 1 , 1

  119. nagina khan
    July 19, 2012 at 4:09 pm

    thanks

  120. prashant
    August 29, 2012 at 2:16 pm

    to find 2nd highest salary
    select max(sal) from emp where sal < ( select max(sal) from emp);

    to find 3rd highest salary
    select max(sal) from emp where sal < ( select max(sal) from emp where sal < ( select max(sal) from emp) )

  121. Neha
    August 31, 2012 at 11:49 am

    you can do it by below query as well
    suppose i have to find out 5th highest salary of the employee

    select Salary from
    (select *,DENSE_RANK() over (order by Salary desc) as ‘num’ from tblEmployee) t1
    where num=5

    it will return the fifth highest salary of the employee

  122. ROHI
    September 11, 2012 at 7:06 am

    NABEEL :
    I really liked this post.

  123. Dharmik
    October 30, 2012 at 2:36 am

    Sir we have table named XYZ having colums name,address ,city
    now i want to find all the records of satellite(area) in the city ahmedabad..

  124. Deepak
    November 28, 2012 at 12:23 pm

    select max(salary) from emp where salary<(select max(salary) from emp)

  125. June 13, 2013 at 12:05 pm

    SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

  126. Nilesh Bedade
    July 4, 2013 at 7:11 am

    “select emp_salary from emp_salary_master order by emp_salary desc limit 2,1″
    -Query for 3rd highest salary

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: