Monday, January 16, 2017

SQL Server- How to find out the nth Highest salary


GO
use tempDB
-- How to Create New Table
CREATE TABLE Employee
(
       EMPID INT
      ,EMPNAME VARCHAR(50)
      ,SAL MONEY
      ,DOJ DATE
      ,LOC VARCHAR(50)
      ,GENDER CHAR(6)
      ,MagrID INT
      ,DEPTNO INT
)
INSERT INTO Employee(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)
VALUES(1,'Santhosh',35000,'01-12-2014','Bangalore','M',3,101)

INSERT INTO Employee
VALUES(2,'Kumari',30000,'11-05-2013','Chennai','F',3,104)

INSERT INTO Employee(EMPID, EMPNAME, SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)
VALUES(3,'Kamesh',75000,'01-12-2012','Mumbai','M',NULL,105)

INSERT INTO Employee(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)
VALUES(4,'Arun',44000,'Hyderabad','m',5,103)
INSERT INTO Employee(EMPID, EMPNAME, SAL, LOC, GENDER, MagrID, DEPTNO)
VALUES(6,'Reddy',44000,'Hyderabad','m',5,103)


INSERT INTO Employee(EMPID, EMPNAME,SAL, DOJ, LOC, GENDER, MagrID, DEPTNO)
VALUES(5,'Aishwarya',60000,'01-12-2014','Bangalore','f',3,103)


SELECT * FROM Employee
go
select * from (
select *,dense_rank() over( order by sal desc) rn   from Employee) rn
where rn=3
go
Select  *
From    Employee E1
Where 3 = (Select Count(Distinct(E2.SAL)) From Employee E2 Where E2.SAL >= E1.SAL)

No comments:

Search This Blog

DAX - Grouping on multiple columns and the count

Please go thorugh the below URL for entire soultion. http://community.powerbi.com/t5/Desktop/DAX-Grouping-on-multiple-columns-and-the-cou...