Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

Day 12: Math functions and operators in PostgreSQL (Part 2)

Dec 12 2011 11:41AM by Niladri Biswas   

Today we will look into the famous problem of "Finding the Second highest salary of Employees for each department"

Well, this question has already been asked in PLSQL Challenge 2 and henceforth we are not going to discuss about the introduction of the same here

Sample Data

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
1           T Cook          Finance         40000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
4           D Adams         Finance         15000.00
5           M Williams      IT              80000.00
6           D Jones         IT              40000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00
9           A Anderson      Back-Office     25000.00
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
12          T Clerk         Back-Office     10000.00

Expected Results

EmployeeID  EmployeeName    Department      Salary   
----------- --------------- --------------- ---------
10          S Martin        Back-Office     15000.00
11          J Garcia        Back-Office     15000.00
2           D Michael       Finance         25000.00
3           A Smith         Finance         25000.00
7           J Miller        IT              50000.00
8           L Lewis         IT              50000.00

Sample Script

CREATE TABLE PLC2_Employees (
	EmployeeID Serial ,
	EmployeeName Character Varying(15),
	Department Character Varying(15),
	Salary NUMERIC(16,2)
);


INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('T Cook','Finance', 40000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Michael','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('A Smith','Finance', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Adams','Finance', 15000);

INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('M Williams','IT', 80000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('D Jones','IT', 40000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('J Miller','IT', 50000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('L Lewis','IT', 50000);

INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('A Anderson','Back-Office', 25000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('S Martin','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('J Garcia','Back-Office', 15000);
INSERT INTO PLC2_Employees(EmployeeName, Department, Salary) VALUES('T Clerk','Back-Office', 10000);

Solution 1: Using Rank Analytical Function

With CTE As(
Select 
	 Rank() Over(Partition By Department Order By Salary Desc) "Rn"
	,* 	
From PLC2_Employees)
Select  
	EmployeeID
	,EmployeeName
	,Department
	,Salary
From CTE
Where "Rn" = 2
Order By 
	Department
	,EmployeeID

Solution 2: Using Dense_Rank Analytical Function

With CTE As(
Select 
	 Dense_Rank() Over(Partition By Department Order By Salary Desc) "Rn"
	,* 	
From PLC2_Employees)
Select  
	EmployeeID
	,EmployeeName
	,Department
	,Salary
From CTE
Where "Rn" = 2
Order By 
	Department
	,EmployeeID

Solution 3: Using Co-related subquery

Select	 
	e1.EmployeeID
	,e1.EmployeeName
	,e1.Department
	,e1.Salary
From  
(
	Select 
		MAX(Salary) Salary
		,Department		
	From PLC2_Employees x
	Where (
		Select 
			MAX(Salary) 
		From PLC2_Employees y
		Where x.department = y.department
	     )> Salary 
		
	Group By Department
) e2
Inner Join PLC2_Employees e1
On e1.Department = e2.Department
And e1.Salary = e2.Salary
Order By 
	e1.Department
	, e1.Salary desc
	,e1.EmployeeID

Solution 4: Using Co-related subquery

Select Distinct *
From PLC2_Employees e1
Where 2 = ( Select Count(Distinct e2.Salary)
            From PLC2_Employees e2
            Where e2.Department = e1.Department
            And e2.Salary >= e1.Salary 
          )
Order By 
	e1.Department
	, e1.Salary desc
	,e1.EmployeeID

Hope this helps

Tags: SQL Server, PostgreSQL, #SQL SERVER,


Niladri Biswas
7 · 21% · 6710
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Day 12: Math functions and operators in PostgreSQL (Part 2)" rated 5 out of 5 by 1 readers
Day 12: Math functions and operators in PostgreSQL (Part 2) , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]