SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

Please read my article here to find Nth Highest Salary of Employee table : SQL SERVER – Query to Retrieve the Nth Maximum value

I have re-wrote the same article here with example of SQL Server 2005 Database AdventureWorks : SQL SERVER – 2005 – Find Nth Highest Record from Database Table

Just a day ago, I have received another script to get the same result from one of the blog reader Pravin Phatangare, let us see his simple method here.

For particular example of employee :

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

You can change and use it for getting nth highest salary from Employee table as follows
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

where n > 1 (n is always greater than one)

Same example converted in SQL Server 2005 to work with Database AdventureWorks.
USE AdventureWorks;
GO
SELECT TOP 1 Rate
FROM (
SELECT DISTINCT TOP 4 Rate
FROM HumanResources.EmployeePayHistory
ORDER BY Rate DESC) A
ORDER BY Rate
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com), Pravin Phatangare

About these ads

326 thoughts on “SQL SERVER – Find Nth Highest Salary of Employee – Query to Retrieve the Nth Maximum value

  1. Hi Pinal,

    myself Dharmendra, and am a Regular reader of ‘SQlAuthority’.
    its a Good one Article about finding nth Higest Salary..
    if am not wrong we can also do the same thing by using aggreate function like Min or Max..

    Like if i want to know 4th Highest Salary then i can

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

    Regards
    Dharmendra k Dixit

  2. how can i get second hight salary or 3 rd hight salary
    here
    iam giving example
    select top 1 quantity from(SELECT TOP n-1 quantity FROM (SELECT DISTINCT TOP n quantity FROM productorder ORDER BY quantity DESC) as A ORDER BY quantity desc )as B order by quantity

    n->represt top nth records
    n-1 is the which highest salary you want

  3. @ Dharmendar

    Bro.. your code is quite long and might be resource consuming because ,

    1. your are selecting the minimum from a whole bunch of values.

    2. You need to write Distinct in the query only then you will give the correct answer.

    Like your code should be :

    SELECT MIN (SALARY )
    FROM UEXAMPLE1
    WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC)

  4. HELLO !
    THIS QUESTION IS DIFFICULT .
    BUT I TRY THE ANSWER
    SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY DESC; ——–> (THIS IS USED FOR RETRIEVE THE MAXIMUM TOP 5 PAID EMPLOYEES )
    OR
    TO RETRIEVE THE MIN VALUE USE THIS
    SELECT TOP 5 * FROM EMPLOYEE ORDER BY EMP_SALARY;

    THANK YOU.

  5. To Imran..

    Yes Bro ..Thanks for ur Suggestion..

    it might be resource Consuming..
    but i think so, its an alternative for finding nth Highest Salary..

    looking forward ur openion..

    Regards
    Dharmendra k Dixit

  6. hi there guys i need a query but i can´t get it
    here i go the table has department_id, salary, employee_id

    how can i increase the salary of all employees of a department in the min salary paid in that department

    as example department 1 the min sal paid is 1000, i have to paid now to each employee his salary +1000

    • hi
      u can try this….

      UPDATE emp
      SET sal = sal +
      (SELECT MIN(sal)
      FROM emp E2
      WHERE e2.deptid = emp.deptid
      GROUP BY deptid)

  7. I think following query will give second highest salary. You can get nth highest by replacing 2 with n.

    select max(sal)from emp e1 where 2<=(select count(*) from
    emp e2 where e1.sal <= e2.sal);

  8. Hi,
    I am Praveen. I am visiting this site for first time. The above query is really good and generic. We can use this also to get nth lowest salary by just changing the order from DESC to ASC.

  9. hi
    i hv tried with this query but i am not able to get the exat answer
    i tried with this query it works fine
    ===========================
    put any digit instead of 5 u can get exat result

    select distinct a.salary from Salary a where 5=( select count(distinct b.salary) from Salary b where a.salary<=b.salary)
    ==================================
    ok friends tell me which query wil be faster
    this query or using top n query
    u can mail me at
    dolagobinda.sahoo@gmail.com for my feed back

  10. For SQL Server 2005 you have a Windowed function ROW_NUMBER, which you can use as follows:

    SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (order by EMP.SAL DESC) AS RANK FROM EMP) v where RANK = 10;

  11. In continuation to my above comment, I agree that this solution might not give exact result while SAL has duplicate values, but again one can play with the query a little bit. and get desired results.

    • SELECT *
      FROM emp e1
      WHERE (1 =
      (SELECT COUNT(DISTINCT sal)
      FROM emp
      WHERE e1.sal <= sal AND e1.deptid = deptid))
      UNION
      SELECT *
      FROM emp e1
      WHERE (2 =
      (SELECT COUNT(DISTINCT sal)
      FROM emp
      WHERE e1.sal <= sal AND e1.deptid = deptid))

  12. Hi friends,

    select max(sal)from emp e1 where 2<=(select count(*) from
    emp e2 where e1.sal <= e2.sal);

    This query is ‘selecting the n th highest salary’. This query is correct.
    I want know how to run this query. please explain this sql query.

    • select * from tablename where sal =(SELECT MIN(sal) FROM tablename
      WHERE sal IN
      (SELECT TOP 3 sal FROM tablename ORDER BY sal desc))

  13. Hi Prasad,

    Hope this will work for you

    CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
    INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
    INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
    INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
    INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
    INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
    INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
    INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
    INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
    INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
    INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)

    SELECT a.ID1, a.[Name], a.Salary AS Salary
    FROM #T1 AS a
    WHERE a.Salary IN (
    SELECT MIN(b.Salary)
    FROM #T1 AS b)
    UNION
    SELECT a.ID1, a.[Name], a.Salary AS Salary
    FROM #T1 AS a
    WHERE a.Salary IN (
    SELECT MAX(b.Salary)
    FROM #T1 AS b)
    ORDER BY a.Salary

    DROP TABLE #T1
    GO

    The above query will also give you List of Employees who has Min Salary and List of employees having Max Highest Salary

    Vamshi

  14. For getting list of employees with Nth Highest Salary, Here is another approach, Probably this would be similar to one of the above queries

    DECLARE @n INT — Variable for accepting N

    SELECT @n = 2 — Get the Second Highest Salary

    CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
    INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
    INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
    INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
    INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
    INSERT INTO #T1 VALUES (5, ‘sssss’, 5000)
    INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
    INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
    INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
    INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
    INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)

    SELECT a.ID1, a.[Name], a.Salary
    FROM #T1 AS a
    WHERE (@n-1) = (
    SELECT COUNT(DISTINCT(b.Salary))
    FROM #T1 AS b
    WHERE b.Salary > a.Salary)

    DROP TABLE #T1
    GO

    Vamshi

  15. This can also be done using DENSE_RANK() function of SQL Server 2005.

    DECLARE @n INT — Variable for accepting N

    SELECT @n = 2 — Get the Second Highest Salary

    CREATE TABLE #T1 (ID1 INT, [Name] NVARCHAR(50), Salary INT)
    INSERT INTO #T1 VALUES (1, ‘Vamshi’, 1000)
    INSERT INTO #T1 VALUES (2, ‘xxxxx’, 2000)
    INSERT INTO #T1 VALUES (3, ‘yyyyy’, 3000)
    INSERT INTO #T1 VALUES (4, ‘zzzzz’, 4000)
    INSERT INTO #T1 VALUES (5, ’sssss’, 5000)
    INSERT INTO #T1 VALUES (6, ‘ccccc’, 6000)
    INSERT INTO #T1 VALUES (7, ‘ppppp’, 2000)
    INSERT INTO #T1 VALUES (8, ‘aaaaa’, 4000)
    INSERT INTO #T1 VALUES (9, ‘bbbbb’, 5000)
    INSERT INTO #T1 VALUES (10, ‘eeeee’, 5000)

    WITH T1 AS
    (SELECT (DENSE_RANK() OVER (ORDER BY Salary DESC)) AS RNum, * FROM #T1)
    SELECT a.ID1, a.[Name], a.Salary
    FROM T1 AS a
    WHERE a.RNum = (@n)

    Vamshi

  16. Hi guys,

    My requirement is like this.

    I need four data belonging to four latest audit dates for every business.

    I used something like…

    SELECT TotalUnitCount FROM @TotalUnitsInLot SecondLatestTotalUnits
    WHERE (2-1) =
    (SELECT COUNT(DISTINCT(LatestTotalUnits.AuditDate))
    FROM @TotalUnitsInLot LatestTotalUnits
    WHERE LatestTotalUnits.AuditDate > SecondLatestTotalUnits.AuditDate
    AND LatestTotalUnits.BusinessId = SecondLatestTotalUnits.BusinessId
    AND LatestTotalUnits.BusinessId = TotalUnits.BusinessId
    GROUP BY BusinessId
    )

    This gives me the results I want, but this is really slow. The problem with

    SELECT TOP 1 TotalUnitCount
    FROM (
    SELECT DISTINCT TOP 4 TotalUnitCount
    FROM TotalUnitsInLot
    ORDER BY TotalUnitCount DESC) a
    ORDER BY TotalUnitCount
    approach is, if there are only 3 audits for a business, this query still gets the data from 3rd audit and gives me those results. If there is no data for the 4th audit I want to show TotalUnitCount = 0 for the fourth audit. How do I do that ?

  17. Give the desired Input and Output clearly in the form of a table. then it would be easy for anyone to answer your question.

  18. my requirement is to get all the emp names ,their respective manager names in a single query..
    like
    empno emp_name Emp_mgr
    101 xyz 105
    102 abc 104
    103 red 101
    104 kfc 105
    105 desa 118

    here i need emp_name and his manager name..like
    emp mgr
    xyz desa
    abc kfc

    • ashish how to find out Avg sal of all employees means like

      manager1 contains 4 employees

      manager2 contains 10 employes

      manager 3 contains 6 emps…ok

      my question is under manager 1 avg sal of 4 emps and mgr 2 avg sal of emp’s….

  19. Hi,

    One question in this query – what if there are multiple employees who has the nth highest salary? How can we get all the employees with the nth highest salary?

    Thanks,
    Kaushik.

    • Hi
      This query gives complete details of employee who are getiting max/min salary

      select * from emp where salary=(select max(salary) from emp);

      select * from emp where salary=(select min(salary) from emp);

      (note: here table name emp,salary is coumn name)

  20. This query gives highest salary for employee and employee name.

    select empsalry,empname
    from empsalr
    where empsalry = (select max(empsalry)
    from empsalr)

  21. Please Help

    I am trying to calculate the Median of DATEDIFFERENCE for each Episode. I have succeeded at obtaining the Difference in dates but am unable to extract the nthsmallest by episode.

    My Query right now is

    SELECT TOP 1 PERCENT DATEDIFF(dd, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE]) AS DIFFERENCE,
    dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO, dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE],
    TABLE2.[Act START DATE] AS [NEXT Session], OPENCLOSE.OPENID
    FROM dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE INNER JOIN
    dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE TABLE2 ON
    dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE] = OPENCLOSE.OPENDATE AND
    dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO = OPENCLOSE.CASENO AND TABLE2.[Act START DATE] > OPENCLOSE.OPENDATE
    GROUP BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE], TABLE2.[Act START DATE], dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.CASENO,
    OPENCLOSE.OPENID
    ORDER BY dbo.RVW_AS_GROUPACTIVITIES_IN_EPISODE.[Act START DATE]

    Sorry if I confuse anyone I am new with SQL I handle Crystal Reports XI and minimal SQL. By the way I am running SQL Enterprise 2000.

    Thank You

  22. I want to know query to get highest paid salary for each department from Dept(Dept type,Id) and Employee table(Name,Emp no,..).

    Aslo want to know query to list all employee from sales Dept from Employee and Dept Table

  23. Question :
    select the name of Manager who has max number of employees .

    (Only the name of manager nothing else)

    solution : ( I am posting my solution and hoping if someone has better one please respond ).

    create table #mgr ( id int not null, name varchar(10) )
    create table #emp ( id int not null, mgrID int, name varchar(10))

    (note: mgrID is foreign key from #mgr )

    insert into #mgr values (1, ‘mgr1′)
    insert into #mgr values (2, ‘mgr2′)
    insert into #mgr values (3, ‘mgr3′)
    insert into #mgr values (4, ‘mgr4′)

    insert into #emp values (1, 1, ‘emp1′)
    insert into #emp values (2, 1, ‘emp2′)
    insert into #emp values (3, 1, ‘emp3′)
    insert into #emp values (4, 1, ‘emp4′)
    insert into #emp values (5, 1, ‘emp5′)
    insert into #emp values (6, 1, ‘emp6′)
    insert into #emp values (7, 1, ‘emp7′)
    insert into #emp values (8, 2, ‘emp8′)
    insert into #emp values (9, 2, ‘emp9′)
    insert into #emp values (10, 2, ‘emp10′)
    insert into #emp values (11, 2, ‘emp11′)
    insert into #emp values (12, 2, ‘emp12′)
    insert into #emp values (13, 2, ‘emp13′)
    insert into #emp values (14, 3, ‘emp14′)
    insert into #emp values (15, 3, ‘emp15′)
    insert into #emp values (16, 3, ‘emp16′)
    insert into #emp values (17, 3, ‘emp17′)
    insert into #emp values (18, 4, ‘emp18′)
    insert into #emp values (19, 4, ‘emp19′)
    insert into #emp values (20, 4, ‘emp20′)

    select m.name from #mgr m
    join
    (
    select m.id, count(m.id) as cnt
    From #mgr m
    join #emp e
    on m.id = e.mgrID
    group by m.id
    ) x
    on m.id = x.id
    Where x.cnt =
    (
    select max(cnt) from
    ( select m.id, count(m.id) as cnt
    From #mgr m
    join #emp e
    on m.id = e.mgrID
    group by m.id
    ) y
    )

  24. how can we get the highest,2nd highest and 3rd highest salary from the employee table along with the employee names in sql

  25. Q)How can we get the highest,2nd highest and 3rd highest salary from the employee table along with the employee names in sql ?

    Ans:-
    select Emp_Sal,Emp_Name from employee where Emp_Sal in (select top 3 Emp_Sal from employee order by Emp_Sal desc);

  26. In the salary senario , you must also take care that salaries can be duplicate in a table , the easiest way to get nth heighest salary is

    select top 1 salary from
    (select top n salary from employee e1 group by e1.salary order by e1.salary desc) e2
    order by salary

  27. Its real simple. Suppose your employees are in a table called by the same name, and the salaries in a column named salaries). Lets See te case in which we want the 3 greatest salaries. Then you Query:

    SELECT employees.salary
    FROM employees
    ORDER BY employees.salary desc
    LIMIT 3;

    If you want the biggest 5 salaries, you put LIMIT 5, and so on….

  28. @Maximiliano Grass

    LIMIT is not ANSI SQL keyword, not untill SQL Server 2005. I tried checking in SQL Server 2008, I could not see it online.

    What I found on internet is LIMIT is a key word used in MYSQL. ( I mean only in MYSQL) no other RDBMS uses it.

    Can you provide more info about this key word. Looks cool.

    Thanks,
    IM.

  29. Hi Pinal Dave,
    i use to read blog past one year.

    #1
    i have the doubt on the finding nth maxi salary.

    every one has their own method even myself too.
    but which one is good performance ?
    i tried to get clear from execution plan but still struggling to find which one is? can you please ?

    #2 how to find the execution performance of a query by looking it.

    just is there any execution time consideration for operators like union , joins etc
    for example: the operator precedence in languages

  30. can anyone tell me what is the meaning of a in the following query and what is the use of it

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

  31. @ Prem,

    Its is nothing but just a subquery.

    If subquery is followed by a FROM clause, SQL Server treat this sub query as a table.

    And in your script, the name of this table (formed by subquery) is a.

    ~ IM.

  32. can anyone tell me what is the meaning of a in the following query and what is the use of it

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

    ” a “–>wat it means

  33. hi pinal sir,

    select top 1
    from
    (select top 3 salary
    from emp35
    order by salary desc) a
    order by salary

    i am not getting result for this query in sqlserver2000 and that to giving error like…i amnt specifyinf attribute name at first line why bcoz i want entire record(i dont want to show single column)

    Server: Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword ‘from’.
    Server: Msg 156, Level 15, State 1, Line 5
    Incorrect syntax near the keyword ‘order’.

  34. @ramu

    The first SELECT does not specify a COLUMN:

    select top 1
    salary
    from
    (select top 3 salary
    from emp35
    order by salary desc) a
    order by salary

  35. hi brain…..thank u for responding but ………i want to get the first record………..how can i by using that query(which is already posted)

  36. @ramu

    The DESC should be supplied in the outer query too.

    The first SELECT does not specify a COLUMN:

    select top 1
    salary
    from
    (select top 3 salary
    from emp35
    order by salary desc) a
    order by salary desc

  37. hi pinal,
    \12353-435764.tif.tif in one column but iwant same thing in another which is existing column \12353-435764.pdf.
    i want only .pdf extension only that to only one time
    if i tis like this i done like

    update tbl
    set col2=replace(col1,’.tif’,’.pdf’)
    \12353-435764.tif
    but i dont know wen it is in \12353-435764.tif.tif
    how to convert into
    \12353-435764.tif.tif–>\12353-435764.pdf
    i amnt able to do like this by using patterns

  38. Hi Sam,

    If you are using SQL SERVER 2005 and above, you can use ROW_NUMBER feature as:

    ;with cte as (
    SELECT ROW_NUMBER() OVER(PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowID,
    *
    FROM TmployeeTable
    )
    SELECT *
    FROM cte
    WHERE RowID = 1

    This will give you list of all details of employee getting highest salary and department wise.

    Let me know if it helps you.

    Thanks,

    Tejas Shah
    (SQL Yoga)

  39. Hi friends ,

    i am new to SQl.. so could u please help me out in finding the second highest and third highest sal .
    And please explain me the query what it does.
    example :

    1> SELECT * from employee e1 WHERE 1=(SELECT COUNT(DISTINCT salary) from employee e2 WHERE e1.salary SELECT TOP 1 salary
    FROM (
    SELECT DISTINCT TOP 6 salary
    FROM employee
    ORDER BY salary DESC) a
    ORDER BY salary
    what u mean by TOP1. Is it a column name in the table or to display in the out put.
    please do explain me the querry.

    Thanks in advance…………..

    Regards,
    Deepak

  40. Some thing missed in the above querry. please do refer to this
    1> SELECT * from employee e1 WHERE 1=(SELECT COUNT(DISTINCT salary) from employee e2 WHERE e1.salary SELECT TOP 1 salary
    FROM (
    SELECT DISTINCT TOP 6 salary
    FROM employee
    ORDER BY salary DESC) a
    ORDER BY salary
    what u mean by TOP1 & TOP6. Is it a column name in the table or to display in the out put.
    please do explain me the querry.

  41. hi this is srinu,
    i have visited ur site multiple times,
    really ur publishing valuable information to beginners like me.

    in this query u used a

    SELECT TOP 1 Sal
    FROM (
    SELECT DISTINCT TOP 2 Sal
    FROM Emp
    ORDER BY Sal DESC) a
    ORDER BY Sal

    why did u use that a variable can u explain me..

    thanks in advance
    regards
    Srinu Ganaparthi

    • Hi Sinu,

      To consider that whole query as single table we need to use that variable..even you can use whatever the variable you like not only a..u can use b..whatever u like

      hope it will give u good idea on that.

  42. @ Srinu

    No need to confuse with the ‘ a ‘ . Its just a alias name given for the table particularly in the case of sub queries. Be aware that we written a sub query to achieve the result.

    If u didn’t specify the name in SQL Server 2000/2005 , it wont give any effect to the result but just observe that when u execute the query system will automatically attach table name_1 alias name to the table.

    I hope u got somewhat clear idea.

    Regards,

    Raj

  43. @ Srinu ,

    Just check this query . It will give 4th highest salary from the list of employee salaries.

    SELECT MIN(empsal) AS Empsalary
    FROM emp
    WHERE (empsal IN
    (SELECT DISTINCT TOP (4) empsal
    FROM emp AS emp_1
    ORDER BY empsal DESC))

    notice that emp_1 is created while executing the query. Its automatically generated one.

  44. @ All

    Thanks guys … for valuable posts … keep posting … its a good sign for us … we can share and solve others problems too….

    regards,

    Raj

  45. i want to retrive nth max sal from emp……….this query gvn eroor please solve it and tell me

    SQL> SELECT TOP 1 sal
    2 FROM (
    3 SELECT DISTINCT TOP 6 sal
    4 FROM emp
    5 ORDER BY sal DESC) a
    6 ORDER BY sal;
    SELECT TOP 1 sal
    *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected

    SQL> SELECT sal
    2 FROM (
    3 SELECT DISTINCT TOP 6 sal
    4 FROM emp
    5 ORDER BY sal DESC) a
    6 ORDER BY sal;
    SELECT DISTINCT TOP 6 sal
    *
    ERROR at line 3:
    ORA-00923: FROM keyword not found where expected

  46. The way to find nth ranking, is with RANK().

    SELECT sal FROM
    (SELECT sal, RANK(ORDER BY sal DESC) rn)
    WHERE rn = rank-you-want

    This will report all people at that level. If, however, you want one and only one, ROW_NUMBER():

    SELECT sal FROM
    (SELECT sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn)
    WHERE rn = rank-you-want

  47. Please explain the following query as in how it is interperated.
    SELECT *
    FROM emp A
    WHERE (n-1) = (
    SELECT COUNT(DISTINCT(B.salary))
    FROM emp B
    WHERE B.salary > A.salary)

  48. Hi, Pinal.
    It is a great article on finding the nth highest value in a column.
    I have a dummy table emp(emp_name,deptname,salary)
    and i want to find out the second highest salary department wise.

    would you please help me to sort it out ?
    anyone’s help will be greatly appreciated..

    Thanks and regards
    Deba

    • @debadutta..

      u can work on this…

      select max(e1.sal), e1.deptno from emp e1

      where 2<=(select count(*) from
      emp e2 where e1.sal <= e2.sal)

      group by deptno;

      u might get ur solution….i supose…just work on this

  49. @debadutta..

    u can work on this…

    select max(e1.sal), e1.deptno from emp e1

    where 2<=(select count(*) from
    emp e2 where e1.sal <= e2.sal)

    group by deptno;

    u might get ur solution….i supose…just work on this

  50. select max(e1.sal), e1.deptno from emp e1

    where 2<=(select count(*) from
    emp e2 where e1.sal <= e2.sal)

    group by deptno;

    can u explain this query how its work

    explain where condiation also ????

  51. @Mookkandi @sharad

    Here’s an explanation of how the following query works:

    For each row, this query makes a cartesian product with other rows of the table. Then according to the inner query syntax, it compares the salaries. Then, the number comparison in the first outer query makes sure that only those records are picked which satisfy the criteria. E.g.

    Let’s say u got 3 records, with salaries 10, 20 and 30. So, the cartesian product is something like,
    1) For row with sal 10 –> 10-10, 10-20, 10-30
    2) For row with sal 20 –> 20-10, 20-20, 20-30
    3) For row with sal 30 –> 30-10, 30-20, 30-30

    Now apply the condition, e1.sal 10-10, 10-20, 10-30 (3)
    2) For row with sal 20 –> 20-10, 20-20, 20-30 (2)
    3) For row with sal 30 –> 30-10, 30-20, 30-30 (1)

    Now the outer condition, 2<= (the count arrived at above). So only 2) and 3) get selected. Hence, it shows salaries according to outer query for these two i.e. 20 and 30.

    PS: This is inefficient and should be avoided. Prefer rank() in Oracle

    Hope it helps.

  52. This query has good execution plan among all the queries explained here .

    Select *
    from (Select *, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Ranks from #T1) a
    WHERE Ranks = N

    Thanks,
    -Amol.

  53. Hey Pinal,
    Good Blog. But just wanted to let you know that there should be a very minute change required in this query:

    USE AdventureWorks;
    GO
    SELECT TOP 1 Rate
    FROM (
    SELECT DISTINCT TOP 4 Rate
    FROM HumanResources.EmployeePayHistory
    ORDER BY Rate DESC) A
    ORDER BY Rate DESC –Desc should be there in order to get top record
    GO

  54. SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);

    enter n value.if u want 3rd max salary place n value 2.

  55. i have question in oracle
    how can i find the second highest manager who has second highest number of employees reporting to him.
    say for eg:
    if mgr 789 has 3 employees reporting to him
    and mgr 889 has 5 employees reporting to him and
    mgr 779 has 2 employees reporting to him

    i need to get mgr 789 as my answer as he has the second highest employees reporting to him

    i need to find out this using sql query.

    pls help

  56. i have a question in sql,any one please help me to get out from here.

    Q.Table name employee in which we have 200 emp list.i want to display the name of the employee who have highest salary from 200 employee table data.

    Please help me to get the answer.

  57. Seriously I want to hug whoever posted this answer originally. THREE DAYS of trying to figure out this code and now it WORKS !!!

    THANK YOU THANK YOU THANK YOU !

    SELECT TOP 1 Invoicetestdate1.[TestDate] AS Expr1
    FROM (SELECT DISTINCT TOP 2 Invoicetestdate1.[TestDate]
    FROM Invoicetestdate1
    GROUP BY Invoicetestdate1.[TestDate]
    ORDER BY Invoicetestdate1.[TestDate] DESC) AS a
    ORDER BY Invoicetestdate1.[TestDate];

    invoicetestdate1 is another query based on a filter generated by a form. But this solution works GREAT !

  58. thanks anurag.it solved my problem.

    here is another q i want to share.

    q.how to do replication in sql server2005 with examle.

    waiting…..

  59. 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….

  60. select * from emp e1 where 2=(select count(*) from
    emp e2 where e2.id > e1.id);

    id values – 1,2,3,4,5

    Here i am getting third highest value

    can any one explain me this query. here what is the use of value 2. and how we are getting third highest value i.e id 3.

    Regards,
    Rock..

  61. Instead of this you should use.

    select Income from (
    select Rank() over(order by Income desc) topRecord, Income
    from (select distinct Income from tblName) tbl ) tbl2
    where topRecord = 2

  62. I am rahul Upadhyay This can be done without using TOP

    SELECT *
    FROM ( SELECT ROWNUM AS rank, Emp_id, Emp_salary
    FROM employees
    ORDER BY Emp_salary
    )
    WHERE rank=3 ;

  63. if you want to display highest salary from emp table
    (display all emp table columns)
    query is

    select * from emp where salary=(select max(salary) from emp);

    here sub query is used
    above query displays complete information available on emp table who are getting highest salary

  64. –First Method
    select top 1 salary from (
    select top 6 salary from employee group by salary order by salary desc) a order by salary
    go
    –Second method
    with nthsalary as
    (
    select salary,row_number() over(order by salary desc) as row from employee group by salary
    )
    select salary from nthsalary where row=6
    GO
    –Thired method
    select distinct salary from employee a where 6=(select count(distinct salary)
    from employee b where a.salary<=b.salary)

  65. Top 3 Salaried Employee from Every Departments

    create table #empdet(
    empno int identity(1,1),
    empname varchar(50),
    sal numeric(18,2),
    dep varchar(20)
    )
    go
    insert into #empdet(empname, sal, dep)
    select ‘A_123′,12000,’BANK’
    Union
    select ‘A_234′,5000,’BANK’
    Union
    select ‘A_345′,10000,’BANK’
    Union
    select ‘A_456′,25000,’BANK’
    Union
    select ‘A_567′,8000,’BANK’
    Union
    select ‘B_123′,25000,’PROG’
    Union
    select ‘B_234′,27000,’PROG’
    Union
    select ‘B_345′,23000,’PROG’
    Union
    select ‘B_456′,13000,’PROG’
    Union
    select ‘B_567′,50000,’PROG’
    Union
    select ‘C_123′,11000,’TEST’
    Union
    select ‘C_234′,9000,’TEST’
    Union
    select ‘C_345′,22000,’TEST’
    Union
    select ‘C_456′,30000,’TEST’
    Union
    select ‘C_567′,8000,’TEST’

    Select * from #empdet
    Select * from #empdet a where empno in (select top 3 empno from #empdet where dep=a.dep order by sal desc)

  66. select top 1 salary from(select distinct top 5 salary from student11 order by salary desc) a order by salary

  67. This is not working for to to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

    Suppose table structure is as follows
    EmpID EmpName Salary
    1 EMP1 2000
    2 EMP2 1000
    3 EMP3 5000
    4 EMP4 4000
    5 EMP5 10000
    6 EMP6 250
    7 EMP7 100
    8 EMP8 2100

  68. Thanks Pinal,

    Your post really helped for fetching nth highest height in SQL Server 2000. I was able to do it in SQL Server 2005 using rowNumber, but was struggling a bit in 2000.

    Thanks again !!
    Sharad

  69. This is one of the best sites which is not only informative but also exceptionally driven with intent to share knowledge.
    Thanks Pinal .

    Thanks and Regards
    Jaiwanth

  70. e.g. to find out 3rd hieghest salary from table abc.

    Select top 1 salary from(select distinct top 3 (salary) from abc order by salary desc)a order by salary asc;

  71. Hi Can anyone tell me what is the function of a in the below query.

    SELECT TOP 1 salary
    FROM (SELECT TOP 1 salary
    FROM employees
    ORDER BY salary DESC) a ——–> ?
    ORDER BY salary

    Query is superb. But i am not understood wat is the use of “a”

    i am expecting a quick reply.. Thq.

    Hey Pinal, thank you for your blog….

  72. hiii….

    plz tell me the sql query for finding that in which departmnet maximum no. of employees are there in employee table of oracle?

  73. plz arrange this in ascending order:–

    abc116
    abc1115
    abc12
    abc11113

    answer should be:

    abc12
    abc116
    abc1115
    abc11113 plz reply me soon..

  74. forget the book

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

    [email addressed removed](amit is back)

  75. 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.salaryselect 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

  76. query to find second highest salary is

    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

  77. query to find the third highest salary is

    forget bookish 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

    [email addressed removed](amy is back)
    Run query then arg

  78. Hello Sir,

    I am great fan of your and fact is am new in sql so please help me

    Find Nth Highest Salary of Employee Without Subquery

  79. Hi pinal ,

    The Above queries works fine if we need only salary to display.

    In case if we want all coloumns then we can try follwing query

    select * from t1
    where c2 =
    (
    select max(c2) from t1 where c2 not in
    ( select top (N-1) c2 from t1 order by c2 desc )
    )

    Where N is the Nth highest salary required.
    The perfromance of this query is really gud as comapre other ones. I have tested this in SQL 2000 and 2005 with 13 lacs records and found that it works much faster then above solution.
    This works for duplicate reocords too.

  80. Hi pinal ,

    The Above queries works fine if we need only salary to display.

    In case if we want all coloumns then we can try follwing query

    select * from Employee
    where Salary =
    (
    select max(Salary) from Employee where Salary not in
    ( select top (N-1) Salary from Employee1 order by Salary desc )
    )

    Where N is the Nth highest salary required.
    The perfromance of this query is really gud as comapre other ones. I have tested this in SQL 2000 and 2005 with 13 lacs records and found that it works much faster then above solution.

    This works for duplicate reocords too.

  81. Dear sir ,
    I simply fire a simple query “Select top 3 netsal from salaryslip” which gives me 1st three records but i want top 3 highest salary records.
    Kindly help me sir

  82. Executed the above query and get the correct result but as performance wise its not correct ,Is there any other way of query to get the Nth Highest value.

  83. public DataTable retrieve()
    {

    SqlCommand cmdret=new SqlCommand(“SELECT TOP 1 salary FROM(SELECT DISTINCT top 2 salary FROM tb_connect ORDER BY salary )a ORDER BY salary desc “,constr);
    SqlDataAdapter da=new SqlDataAdapter(cmdret);
    DataSet dts=new DataSet();
    try
    {

    da.Fill(dts,”tb_connect”);
    return dts.Tables["tb_connect"];
    }
    catch
    {
    throw;
    }
    finally
    {
    constr.Close();
    }

    it will gives second lowest salary

  84. highest 4 th salary from tb_connect table

    SqlCommand cmdret=new SqlCommand
    (“SELECT TOP 1 salary FROM
    (SELECT DISTINCT top 4 salary FROM tb_connect ORDER BY salary desc )
    a ORDER BY salary “,constr);

  85. Hi,
    I have 2 tables
    Staff
    ——
    PID (primary key)
    Firstname
    LastName
    Country
    Postal Code
    City
    Street
    DateofBirth
    Salary

    TelephoneNo
    ——————-
    PID (primary key)
    TelephoneNo (also primary key)

    My question is -> Give a list of telephone number of Susan Winter (Remark: don’t specify the internal PID!- this confused me )

    Can anyone help solve this please. i am beginner to SQL.

    This forum helped me a lot. Thanks every one who posted :-)

    • @Tash

      Didn;t get that question, Is this what you are looking ?

      Select TelephoneNo
      From Staff A
      join TelephoneNo B on A.PID = B.PID
      Where A.FirstName +’ ‘+A.LastName = @Name

      ~IM.

  86. ———————————Try this———————————

    select distinct(e.salary) from
    (select salary,dense_rank() over(order by salary desc)R from EMP)e
    where e.R=3

  87. ———————————Try this———————————

    ———————————For Nth max———————————

    select distinct(e.salary) from
    (select salary,dense_rank() over(order by salary desc)R from EMP)e
    where e.R=N

  88. Any answer this question?

    In oracle “TOP” keyword is there or not. And it is working in SQL queries.

    Advance thanks
    sreenivas

  89. hi Pinal,

    i want to know how to find the second highest salary for each department from emp table. if we have table like this:

    name dept_name salary
    ——– ————— ———
    raj Account 15000
    sam Account 20000
    roy Admin 8500
    paul Admin 9000
    jack Admin 12000
    ram IT 25000

  90. Sometime the Basic of SqlQuery get Confused me
    I try your above query
    by creating following db
    dbo.Test
    Create Table Test (Name Varchar(50), Salary Numeric(18,2))
    Insert Into Test (Name,Salary) Values (‘A’, 100)
    Insert Into Test (Name,Salary) Values (‘B’, 90)
    Insert Into Test (Name,Salary) Values (‘C’, 80)
    Insert Into Test (Name,Salary) Values (‘D’, 70)
    Insert Into Test (Name,Salary) Values (‘E’, 60)
    Insert Into Test (Name,Salary) Values (‘F’, 50)
    Insert Into Test (Name,Salary) Values (‘G’, 40)
    Insert Into Test (Name,Salary) Values (‘H’, 30)
    Insert Into Test (Name,Salary) Values (‘I’, 20)
    Now as Per your above query i fired the same in my ssms
    i get the result
    (6th Highest)
    50
    but the query seems to be confuse me

    SELECT dISTINCT TOP 6
    SALARY
    FROM TEST
    ORDER BY SALARY DESC

    100.00
    90.00
    80.00
    70.00
    60.00
    50.00

    now apply your query method

    SELECT TOP 1 SALARY FROM(
    SELECT dISTINCT TOP 6
    SALARY
    FROM TEST
    ORDER BY SALARY DESC
    ) A
    ORDER BY SALARY

    i get the result 50
    but in subquery i get the following ans

    100.00
    90.00
    80.00
    70.00
    60.00
    50.00

    then how can top 1 work because in subquery 100 is first na then why we get the result 50. Please help me

  91. i have a doubt in sql.
    we have a employee table and having fields empno, empname, and managername. i want to retrieve managername who has more than 50 employees working under him

    pls anybody knows pls send me

  92. Following is the simple query to get the 3rd highest salary.

    select distinct min(sal) from (select sal from(select sal from employee order by sal desc)where rownum<=3)

    Instead of 3, we can substitute n values.

  93. @sai

    Following query will retrieve the managername who has more than 50 employees working under him.

    select managername from employee group by managername having count(*)>50;

  94. this worked for me :
    select top 5 Salary from (SELECT TOP 5 * FROM employeetable ORDER BY employeetable.salary DESC) a ORDER BY salary

  95. hey sir i have a query here which worked but i don’t understand how….please help me

    mysql> select * from emp a
    where 2>(select count( distinct(sal))from emp where sal>a.sal and a.deptno=deptno )
    order by deptno;

    it is the query to find two highest salaries in every department

  96. hiii sir i have a query to find nth salary from employee table
    its working correctly
    please explain me how it is working

    thanks

  97. select distinct salary
    from employee e
    where
    4=(select count(distinct salary) from employee where e.salary<=salary);

    please explain the working of this query

    nitendra kumar

  98. hey i have a ques…
    i have a table with studentdetails having columns id,name,technology.. and another table course having column name technology,count..
    the 2nd table course do not have any values whenever a row is inserted in studentdetails the corresponding technology is inserted in course table n if it is already present its count shud be updated … i need to create trigger for this query,

  99. can we type in this following querry to find the second max salary from employee table

    select max(salary-1) from employee;

    how is this query different from above mentioned queries to find the second max salary from employee table??

    please reply

    thanks

    Pramod

  100. 3rd highest salary:

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

  101. Hi,
    My name is Bernard from Brooklyn New York. The following example I used a CTE along with a windowing/ranking function in an attempt to solve the problem.

    Creat table sal

    (ID INT IDENTITY PRIMARY KEY,
    LNAME VARCHAR(25) NOT NULL,
    FNAME VARCHAR(25) NOT NULL,
    SAL INT NOT NULL
    )

    GO

    –USE A ROW CONSTRUCTOR FUNCTION TO ADD THE DATA INTO THE TABLE

    INSERT INTO SAL
    (VALUES
    (‘WILLIAMS’,’JOHN’,36000),
    (‘STONE’,’BILLY’,45000),
    (‘RAMSEY’,’PAULA’,75000),
    (‘MURPHY’,’BERNARD’,100000));

    WITH NARDO

    AS

    (SELECT EMPNO, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS ‘NTHSALARY’

    FROM SAL

    )

    SELECT EMPNO, SAL, NTHSALARY

    FROM NARDO

    WHERE NTHSALARY = 2

  102. Hi Bernard,

    This is Dhirendra, in Sql server 2008 the insert statement is not working properly as per your query otherwise the concept of using Common Table Expression(CTE) is quite good. You can insert data in this manner too:
    Create table sal

    (ID INT IDENTITY PRIMARY KEY,
    LNAME VARCHAR(25) NOT NULL,
    FNAME VARCHAR(25) NOT NULL,
    SAL INT NOT NULL
    )

    GO
    INSERT INTO SAL(LNAME,FNAME,SAL)
    select ‘WILLIAMS’,’JHON’,3600
    union all
    select ‘STONE’,’BILLY’,45000
    union all
    select ‘RAMSEY’,’PAULA’,75000
    UNION ALL
    SELECT ‘MURPHY’,’BERNARD’,10000

    WITH NARDO

    AS

    (SELECT ID, SAL, ROW_NUMBER() OVER (ORDER BY SAL DESC) AS NTHSALARY

    FROM SAL)

    SELECT ID, SAL, NTHSALARY
    FROM NARDO WHERE NTHSALARY = 2

    Thanks,
    Dhirendra

  103. Display the 3rd highest salary. using this query we can find out Nth maximum value. in the 1st where clause put the number you want to display.

    select salary from(select salary from(select salary from employee
    order by salary desc)
    where rownum<=3
    order by salary)
    where rownum=1;

  104. SELECT max(age) FROM Student
    WHERE age < ( SELECT max(age) FROM Student
    WHERE age < ( SELECT max(age) FROM Student
    WHERE age < ( SELECT max(age) FROM Student )));

  105. Hi,
    You can do the same thing in good manner with CTE (Commom table expression)

    with cte
    as
    (
    select age,dense_rank()over(order by age desc) as row from e
    )
    select * from cte where row=2;

    Thanks,
    Dhirendra

  106. I just need to know how to increase a salary by 10% using SQL? What command do I need to use…. Increase all employees’ salaries with the selected EEO-1 classification by 10%.

  107. Hi Pinal,

    I am Hitesh Shah (Software testing profile), I am new in this Blog , and I want simple query which I can understand and speak in interview.

    How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table?

    Thanks
    Hitesh Shah

  108. Hai,i thought it will help .you to find the any highest sal as per requirement.i face the question on interview.

    Select * from tablename e1 where N=(select count(distinct (e2.sal))from tablename e2 where e2.sal>=e1.sal

    ex-

    select * from emp e1 where 3=(select count(distinct(e2.sal)) from emp e2 where e2.sal>=e1.sal)

    this above query is used to find the 3 max salary of table

  109. Hi to all

    this is siva prasad.in above some quereis were not worked properly ,in the sense i am unable to get desired output when some are duplicated(sal is repeated for some records).

    Finally I executed a query .It doesn’t have order by clause of-course it has distinct.

    SELECT Max(Sal) FROM emp
    WHERE Sal IN
    (SELECT distinct TOP 3 Sal FROM emp )

    and the query in the Main article is yielding correct result.

  110. hi

    this is the query to retrieve n th max salary record details
    SELECT * FROM
    (
    SELECT DENSE_RANK() OVER(ORDER BY [Sal] DESC)AS RowId,*
    FROM [dbo].[Emp]
    ) AS e1
    WHERE e1.RowId = 4

  111. i have three departments in my table viz cse,ec , it now i have to retrieve name of three persons who are getting maximum salary in their respective department

    suppose my table is like this

    name salary department

    ashish 15000 cse
    suresh 14000 it
    mahesh 12000 it
    ramesh 9000 cse
    abhor 10000 ec
    adhir 8000 ec
    ankit 16000 cse

    and i want result as

    name salary department

    ankit 16000 cse
    suresh 14000 it
    abhor 10000 ec

    can i achieve this in a single query please help me out

  112. we can also find the nth salary as……..

    select * from TableName E where n-1=(select COUNT(distinct SAL) from TableName where SAL>E.SAL)

  113. Hi friend This will also helpful.

    Here employeemaster is table nane
    gross is column name

    select top(1) gross from employeemaster where gross not in(select distinct top (4) gross from employeemaster order by gross desc) order by gross desc

    • drop is to drop a table like drop table tablename we can aso drop a store procedure,like drop proc pocname,.
      delete is to delete the record.like:delete from tablename where id=1,
      truncate is to completly eradicate all the records.
      hope this will help u.

  114. I think this will be the exact query: (n > 1)

    select * from products where unitprice=(select min(tmp.unitprice) from (select top n unitprice from products order by unitprice desc) as tmp);

    e.g. (Fifth highest) :-

    select * from products where unitprice=(select min(tmp.unitprice) from (select top 5 unitprice from products order by unitprice desc) as tmp);

  115. 1st,2nd,….Nth (the following query maximum of salary using row_number() function)

    SELECT A.SALARY FROM(SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) ROWID,* FROM EMP) A WHERE A.ROWID=N

    IF N=1 THEN 1ST MAXIMUM
    IF N=2 THEN 2ND MAXIMUM
    .
    .
    .
    .
    .
    .

  116. i have a one table marks1,Marks2,Marks3 as coulmns,,
    now i want to get least Marks form entire table, not at in particular row/Coulmn

    how can i get..
    reply me sir…

    • Hi Rajani,

      I have gone through the question that you have given. I really found this interesting and here is the solution for you.

      Select top 1 *,
      Case When marks1< Marks2 And marks1< Marks3 Then marks1
      When Marks2 < marks1 And Marks2< Marks3 Then Marks2
      Else Marks3
      End As MinMark
      From TableNameHere

  117. hello to all

    Select query without using “Like” operator . For eg select employee name start with ‘j’ and city is ‘Noida’ in company_name

    • Hello Rama,

      I had gone through the question that you have given and found this solution for your question:

      SELECT SUBSTRING(emailID,NULLIF(PATINDEX(‘%s%’,emailID),0),3) AS EMAIL FROM CustomerDetails

      This will definitely solve your problem.

      Thanks & Regards
      Sisir Patro

  118. .
    .
    . 2nd highest value ????????????????

    select max(salary) as ‘second highest salary’ from tbemployee where salary not in

    (
    select top 2 (salary) from tbemployee order by salary desc
    )

    • Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)

      INSERT INTO #EMP
      SELECT 1,’VINAY’,15000.00 UNION
      SELECT 2,’KESAV’,52890.00 UNION
      SELECT 3,’HANU’,24000.00 UNION
      SELECT 4,’ARUN’,42200.00 UNION
      SELECT 5,’KISHORE’,25000.00 UNION
      SELECT 6,’VINOD’,32000.00 UNION
      SELECT 7,’SENDIL’,22890.00 UNION
      SELECT 8,’RAKESH’,26400.00 UNION
      SELECT 9,’SURYA’,25200.00 UNION
      SELECT 10,’SIVA’,28900.00

      SELECT * FROM #EMP

      SELECT * FROM #EMP ORDER BY SAL DESC

      –TO GET ONLY TOP 5-10 HIGHEST SALARIES
      SELECT TOP 5 SAL FROM #EMP WHERE ENO NOT IN
      (SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
      ORDER BY SAL DESC

      –TO GET ONLY TOP 5-10 HIGHEST SALARIED EMPLOYEE DETAILS
      SELECT TOP 5 * FROM #EMP WHERE ENO NOT IN
      (SELECT TOP 5 ENO FROM #EMP ORDER BY SAL DESC)
      ORDER BY SAL DESC

      DROP TABLE #EMP

    • SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RN,* FROM #EMP) AS T WHERE T.RN BETWEEN 6 and 10
      –also you can use

  119. “select max(salary) from (select distinct top 5 salary from Emp_Id order by salary desc) a “…why we have used ‘a’ after the subquery..will anyone pls tell me..

  120. Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
    INSERT INTO #EMP
    SELECT 1,’VINAY’,15000.00 UNION
    SELECT 2,’KESAV’,52890.00 UNION
    SELECT 3,’HANU’,24000.00 UNION
    SELECT 4,’ARUN’,42200.00 UNION
    SELECT 5,’KISHORE’,25000.00

    SELECT * FROM #EMP

    SELECT TOP 1 SAL FROM #EMP WHERE ENO NOT IN
    (SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
    ORDER BY SAL DESC– To get only 2nd highest salary

    SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
    (SELECT TOP 1 ENO FROM #EMP ORDER BY SAL DESC)
    ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details

    DROP TABLE #EMP

    • SELECT TOP 1 * FROM #EMP WHERE ENO NOT IN
      (SELECT TOP 2 ENO FROM #EMP ORDER BY SAL DESC)
      ORDER BY SAL DESC– To get the details of 2nd highest salaried emploee details

      even using Row_Number you can do this

      SELECT ENO,ENAME,SAL FROM
      (SELECT Row_Number() OVER (ORDER BY SAL DESC) AS RN,*
      FROM #EMP) E WHERE E.RN =3

      – To get the details of 3nd highest salaried emploee details

      • Hi Purna,

        Your answer will not work when there is multiple record with same sal
        for example:
        if there is records like:

        ENO ENAME SAL
        1 ABC 3000
        2 XYZ 3000
        3 PQR 2000
        4 DEF 1000
        This time it will give record 3 as answer
        I have solution of it:
        with result AS
        (
        select maths,RANK() over (order by maths desc) as row from tblResult group by maths
        )
        select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
        in this query instead of result write your tablename instead of maths write your column name instead of 3 write your number which you want

        Thanks

        Give your response is this helpful or not

        • Hi Mr.DK,

          Absolutely correct. But I’ll update my query now to this

          Create table #EMP(ENO INT,ENAME NVARCHAR(20),SAL FLOAT)
          INSERT INTO #EMP
          SELECT 1,’VINAY’,3000.00 UNION
          SELECT 2,’KESAV’,3000.00 UNION
          SELECT 3,’HANU’,2000.00 UNION
          SELECT 4,’ARUN’,2000.00 UNION
          SELECT 5,’Sanood’,5000.00 UNION
          SELECT 6,’Vijay’,1000.00

          SELECT ENO,ENAME,SAL FROM
          (SELECT Dense_Rank() OVER (ORDER BY SAL DESC) AS RN,* FROM #EMP) E WHERE E.RN =3

          Even this will work perfectly then Rank() function when there is multiple values with same Sal then Rank will escape some values

          result:
          ENO ENAME SAL
          3 HANU 2000
          4 ARUN 2000

  121. Hi your query is right giving correct i have another solution
    with result AS
    (
    select maths,RANK() over (order by maths desc) as row from tblResult group by maths
    )
    select * from result r inner join tblResult t on r.maths = t.Maths where ROW = 3
    in this query instead of result write your tablename instead of maths write your column name instead of 3 write your number which you want

    Thanks

  122. Q1. SELECT TOP 1 salary
    FROM (SELECT DISTINCT TOP 6 salary FROM employee
    ORDER BY salary DESC) a
    ORDER BY salary

    what does this ‘ a ‘ means here.

    Q2. How to auto number the rows in a table in Oracle SQL*9 ?

    Q3. How to execute the query to find the 3rd highest salary of a employee query in Oracle SQL*9 ?

    Q3. Write a sql query to display employee name with grade name as a column. If a employee having salary greater than or equal to 5000 than the grade = GOLD otherwise SILVER ?

    Hint : u have a Employee(empid,empname,salary) table

    Please HELP ASAP!!!


    Thanks in advance!
    Vijesh Jain

  123. you can do it by below query as well
    suppose i have to find out the fifth highest salary of an employee
    then

    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 an employee

  124. sir please tell me suppose election polloing application that different categories
    and find out highest votes in one by one categories

  125. select top 1 salary
    from ( select distinct top 2 salary
    from Salary order by salary desc)a order by salary what is meaning of a after from () a order by salary

  126. SELECT MIN (SALARY )
    FROM UEXAMPLE1
    WHERE SALARY IN (SELECT DISTINCT TOP 4 SALARY FROM UEXAMPLE1 ORDER BY SALARY DESC),

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

    both the queries are not woking in oracle it gives the error FROM keyword not found where expected

  127. (TableName=Student, ColumnName=Mark) :=>

    select *from student where mark=(select mark from(select row_number() over (order by mark desc) as t,mark from student group by mark) as td where t=2)

  128. @Pinal

    Thanks for different way of writing query but how would you find 2nd highest salary of employees, if 2 or more employees having same salary which is second highest salary

    Please let me know

    Thanks,
    Amol P

  129. select MIN(STUDENT_ID) from TBL_STUDENT where STUDENT_ID in( select distinct top N student_id from TBL_STUDENT order by STUDENT_ID desc)

  130. Hi all,

    Help me to write an sql query ..

    suppose i have 4 departments. I need max salary in department(1) and 2nd highest salary in department(2) and third highest in dept(3) and least sal in 4th dept.

    my table is like this

    name sal dept
    A 1000 1
    B 1600 1
    C 950 1
    D 1050 1
    E 960 2
    F 800 2
    G 840 2
    H 1010 2
    I 999 3
    J 970 3
    K 1000 3
    L 940 3
    M 1200 4
    N 1050 4
    P 888 4
    R 799 4

    I WANT THE OUTPUT LIKE THIS:

    NAME SAL DEPT
    B 1600 1
    E 960 2 — SECOND HIGHEST IN DEPT
    J 970 3 — THIRD HIGHEST
    R 799 4 — 4TH HIGHEST

    Is it possible to do it in SQL or we have to go for PLSQL. Please reply..

    thanks in advance….

  131. Second Last Greater Number
    SELECT GrandTotal FROM
    ( SELECT GrandTotal,ROW_NUMBER() OVER(ORDER BY GrandTotal desc) As RowNum
    FROM def_ServiceProvider ) As A
    WHERE A.RowNum IN (2,3)

  132. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  133. Consider you want the second highest salary.

    ———————————————–
    DECLARE @nth int;
    SET @nth=2;
    ———————————————–

    This example returns the nth highest salary from the employee table if the nth highest salary exists; else it returns -1.
    ———————————————–
    SELECT (CASE WHEN COUNT(*)=@nth THEN MIN(salary) ELSE -1 END) AS salary
    FROM
    (
    SELECT DISTINCT TOP (@nth) salary
    FROM Employee
    ORDER BY salary DESC
    ) a
    ———————————————–

    NOTE: If you want to return NULL, replace -1 for NULL.

  134. how we will run this query “Select min(Employee_Salary) from Employee_Test where
    Employee_Salary IN(Select Distinct top 2 Employee_Salary from Employee_Test order by Employee_Salary desc );” in my sql as my sql does not support top keyword

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