Interviews - C# Corner

Ravi Patel
select 2nd highest salary
By Ravi Patel in Databases & DBA onApr 19 2014
  • Ramesh  Maruthi
    Aug, 2014 11

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

    • 3
  • Rajesh Pathakoti
    Jul, 2014 16

    The simplest way isselect max(sal) from Salary where sal in(select top 2 sal from Salary order by sal desc).

    • 2
  • Ranjit Powar
    Apr, 2014 24

    Select Salary from Employee A where 2=(select COUNT(*) from Employee B where B.Salary>=A.Salary)

    • 2
  • Sushant Shinde
    Mar, 2019 16

    Hello friendI will show you very simple way where in future you can easily find nth Highest Salary.Lets get started : Step 1 : Create Table EmployeeCREATE TABLE [dbo].[Employee] ([Eid] [int] NULL,[EName] [varchar](50) NULL,[Salary] [money] NULL ) Step 2 : Insert some DummyDataInsert into Employee(Eid,EName,Salary)Values(1,'Employee1',3000) Insert into Employee(Eid,EName,Salary)Values(2,'Employee2',5000) Insert into Employee(Eid,EName,Salary)Values(3,'Employee3',4000) Insert into Employee(Eid,EName,Salary)Values(4,'Employee4',1000) Insert into Employee(Eid,EName,Salary)Values(5,'Employee5',2000)Step 3 : Query to fetch Data- Here we will create one Maintable and one Auxillary table and correlate the Query with the same table.Select * from Employee E1 where 2 = (Select count(*) from Employee E2 where E2.Salary >= E1.Salary)Explation of Query :1st record of Maintable(E1) i.e 3000 compared with all records from AuxillaryTable(E2) 3000>=3000 - true - count 1 5000>=3000 - true - count 2 4000>=3000 - true - count 3 1000>=3000 - false - count 3 --- Count Remains Same 2000>=3000 - false - count 3 --- Count Remains Same In this case count is 3 so condition wont match, now move to next Record2nd record of Maintable(E1) i.e 5000 compared with all records from AuxillaryTable(E2) 3000>=5000 - false - count 0 5000>=5000 - true - count 1 4000>=5000 - false - count 1 --- Count Remains Same 1000>=5000 - false - count 1 --- Count Remains Same 2000>=5000 - false - count 1 --- Count Remains Same In this case count is 1 so condition wont match, now move to next Record3rd record of Maintable(E1) i.e 4000 compared with all records from AuxillaryTable(E2) 3000>=4000 - false - count 0 5000>=4000 - true - count 1 4000>=4000 - true - count 2 1000>=4000 - false - count 2 --- Count Remains Same 2000>=4000 - false - count 2 --- Count Remains Same In this case count is 2 so condition matches as we needed the 2nd highest Salary Hence the 2nd Higest salary is 4000If any Query feel free to ask.Thanks

    • 0
  • vicky bhoir
    Feb, 2017 24

    Select max(salary) from (select top 2 distinct salary from table order by desc )

    • 0
  • Jiseo Lee
    Oct, 2016 18

    ??? runman7942.com ?? ???? ???? ?? ????

    • 0
  • Rahul Prajapat
    Jun, 2015 10

    Select Top 1 Salary From ( select top 2 salary from Table_name order by(salary) Desc )A Order By(Salary) Asc

    • 0
  • Pankaj  Kumar Choudhary
    May, 2015 29

    Select Top 1 Salary From ( select top 2 salary from Table_name order by(salary) Desc )AOrder By(Salary) Asc

    • 0
  • Satyam Verma
    Apr, 2015 9

    select * from table order by salary desc offset 2 rows fetch next 1 rows only

    • 0
  • Tanul Bhasin
    May, 2014 1

    SELECT DISTINCT TOP 2E.Salary FROM Employee E EXCEPT SELECTMAX(Salary) FROM Employee ORDER BY E.Salary DESC

    • 0
  • Rajesh  kumar
    Apr, 2014 28

    Select Amount from [PurchaseDetails] A where 2=(select COUNT(*) from [PurchaseDetails] B where B.Amount>=A.Amount) note :-PurchaseDetails is a table and Amount is a column more help contact:----- rajeshsinghniit1@gmail.com

    • 0
  • swatantra singh
    Apr, 2014 26

    Select * from orders(NOLocK) order by Freight desc; WITH CTE as ( select ROW_NUMBER() Over (Order by Freight DESC) as RowNum,OrderID, CustomerID, Freight, EmployeeID from Orders) Select * FRom CTE where RowNum=2The above example is from northwind database

    • 0
  • Ashish
    Apr, 2014 22

    select max(salary) from employee where emp_id not in (select TOP(n) from employee order by salary desc )In your case n =1,as you want 2nd highest salary .To get 3 highest or so on just replace n acordingly.

    • 0
  • Munesh Sharma
    Apr, 2014 22

    SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum < 3)SQL Queries in SQL Server, select min(SALARY) from (select top 2 * from employee) aSQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit 2) afor more imp Sql query http://dotnet-munesh.blogspot.in/2013/12/important-sql-query.html

    • 0

Most Popular Companies

Most Popular Job Functions

MOST LIKED QUESTIONS