SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
The simplest way isselect max(sal) from Salary where sal in(select top 2 sal from Salary order by sal desc).
Select Salary from Employee A where 2=(select COUNT(*) from Employee B where B.Salary>=A.Salary)
Select Top 1 Salary From ( select top 2 salary from Table_name order by(salary) Desc )A Order By(Salary) Asc
Select top 1 salary from employee where salary in (select top 2 salary from employee order by Salary desc )
You can do this use dense_rank(),using dense_rank() you can find nth highest salarywith cte as(Select Salary,dense_rank() over (Order by Salary desc) as rankSal)Select distinct salary from cte where rankSal=2
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
Select max(salary) from (select top 2 distinct salary from table order by desc )
??? runman7942.com ?? ???? ???? ?? ????
Select Top 1 Salary From ( select top 2 salary from Table_name order by(salary) Desc )AOrder By(Salary) Asc
select * from table order by salary desc offset 2 rows fetch next 1 rows only
SELECT DISTINCT TOP 2E.Salary FROM Employee E EXCEPT SELECTMAX(Salary) FROM Employee ORDER BY E.Salary DESC
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:----- [email protected]
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
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.
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