I worked very hard to make this article easy to understand. If you face any problem in understanding this article please point out the matter. I will be very glad to clear your doubts.
Finding The Employee with the Maximum Salary:
Use Pubs
Go
Create table Employee
(
EmpID int,
EmpName varchar(10),
EmpSalary money
)
Go
Insert into Employee values (1,'Shilpa',2874)
Insert into Employee values (2,'Mahesh',3712)
Insert into Employee values (3,'Vineela',4222)
Insert into Employee values (4,'Supriya',6500)
Insert into Employee values (5,'Latha',6522)
Go
The Query for the Maximum Salary goes as follows:
Select *
From Employee
Where Empsalary = (Select max(EmpSalary)
From Employee)
Explanation:
The SQL Server Engine verifies the inner query and then moves to the outer query.
So, in the above example inner query i.e. Select Max(EmpSalary) from Employee is evaluated first.
This query will return a value of 6522.
This value is substituted in the outer query and it is evaluated as:
Select *
From Employee
Where Empsalary = (6522)
Result:
EmpID EmpName EmpSalary
5 Latha 6522
Above query displays only the First Maximum Salary.
In the case of second, third and fourth level maximum salaries the query will be as below.
Select *
From Employee E1
Where (N-1) = (Select Count(Distinct(E2.EmpSalary))
From Employee E2
Where E2.EmpSalary > E1.EmpSalary)
Explanation:
In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row. ‘N’ is the level of the salary we need.
Example:
Finding the third maximum salary:
Select *
From Employee E1
Where (3-1) = (Select Count(Distinct(E2.EmpSalary))
From Employee E2
Where E2.EmpSalary > E1.EmpSalary)
Result :
EmpID EmpName EmpSalary
3 Vineela 4222
|
No responses found. Be the first to respond and make money from revenue sharing program.
|