17 September 2010

To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

To Find Highest Salary, nth Highest Salary, Lowest Salary, nth Lowest Salary

Correlated sub queries are more generalized to find n'th highest or nth lowest salary.

To Find Maximum Salary

SELECT * FROM `student` WHERE mark=(select max(mark) from student)

n means 1 st, 2nd, 3rd highest or lowest salary dont give n values,Give n-1 values
For eg
please give

(if you want 2nd highest salary give n=1
for 3rd n=2 like that.)

To find nth highest salary


SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

To find 2nd highest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary < E2.Salary)

select top 2 * from Trainee where Salary in(select top 1 Salary from Depart)order by Salary DESC

To find 2nd lowest salary

SELECT * FROM Employee E1 WHERE 1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

To find nth lowest salary

SELECT * FROM Employee E1 WHERE n-1 =
(SELECT COUNT(DISTINCT Salary) FROM Employee E2 WHERE E1.Salary > E2.Salary)

3 comments:

  1. --To find second maximum
    select max(classid) from mclass where classid not in(
    Select max(classid) from mclass)

    ReplyDelete
  2. --To find second Minimum
    select min(classid) from mclass where classid not in(Select min(classid) from mclass)

    ReplyDelete
  3. -- To find the nth minimum classid - replace 7 with nth
    select max(classid) from mclass where classid in(
    select top 7 classid from mclass order by classid)

    ReplyDelete

Comments Welcome

Implementing OAuth validation in a Web API

 I mplementing OAuth validation in a Web API Implementing OAuth validation in a Web API using C# typically involves several key steps to sec...