How to find Nth highest and lowest salary in SQL

This is the one of the most common question is asked in SQL. Once I was in an interview, the same question was asked  to me.

I knew the answer so I wrote the query to find the Nth highest salary in a sql table. I used the top keyword to write the sql query.
But after that interviewer asked me, "Could you please write the query without using TOP key word?". I did not know the answer.

After interview got finished, I searched for the answer and I found some interesting articles.

So here I am posting the method to find out the Nth highest and lowest salary in a sql table with both, using TOP keyword and without using it, methods.

So let's start.

Here I have a sql table which is having employees names and their salaries.

To find out the Nth highest salary (For Example: here I am finding 3rd highest salary) I wrote  the query like below

SELECT  TOP 1 Salary AS 'Higest Salary',Name FROM (SELECT DISTINCT TOP 3 Salary,Name FROM tblSalary ORDER BY Salary DESC) a ORDER BY Salary ASC

and the result is :



To find out the Nth lowest salary (For Example: here I am finding 3rd lowest salary) I wrote the query like below

SELECT TOP 1 Salary AS 'Lowest Salary',Name FROM (SELECT DISTINCT TOP 3 salary,Name FROM tblSalary ORDER BY Salary ASC) a ORDER BY Salary DESC

Result:

 Now I founded the query for the same but without using TOP keyword.

SELECT Name, Salary FROM tblSalary a1 WHERE N-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary < OR > a1.Salary)
 
You just need to replace "N" with the "Number", like below I need to find out the 3rd highest and lowest salary, so I replaced N with 3.

To find 3rd highest salary

SELECT Name, Salary FROM tblSalary a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary > a1.Salary)    

Result: 



To find 3rd lowest salary

SELECT Name, Salary FROM tblSalary a1 WHERE 3-1= (SELECT COUNT(DISTINCT Salary) FROM tblSalary a2 WHERE a2.Salary < a1.Salary) 
 
Result:



I hope this would be helpful.

Thanks

Abstract Classes in C#

Hi guys
In this post we will learn about abstract classes in C#.

What do you mean by word “Abstract”?

Abstract means (as a noun) summary not a whole story.

"Take vehicle and car, vehicle is an abstract of car."

So in an abstract class we have abstract and non-abstract methods. In the class abstract methods don’t have implementation; we can implement those later in derived classes.

We can create an abstract class through using the abstract keyword. We cannot have an instance of abstract class.
If we try to make an instance of abstract class we get a compile time error "cannnot create an instance of abstract class or interface".

So why we need abstract classes? We can have abstract classes just for inheritance. It provides the a common pattern to all derived classes.

Some basic facts about abstract class

• It is mandatory to override all abstract method in the derived class.
• Abstract classes are only for inheritance. 
• An abstract class can also contain methods with complete implementation, besides abstract methods.
• Abstract classes have effect only when used with inheritance.
• An abstract member is not implemented in the base class and must be implemented in derived classes.
• A member defined as virtual must be implemented in the base class, but may be optionally overridden in the derived class if different behavior is required.
• An abstract class cannot support multiple inheritances.
• Abstract methods cannot have body.

Here is an example code of abstract class and it's output.

 using System;  
 namespace ConsoleApplication1  
 {  
   abstract class AbsClass  //declaring abstract class  
   {  
     //declaring abstract methods  
     public abstract int MultiplyTwoNumbers(int x, int y);  
     public abstract int SubtractTwoNumbers(int p, int q);  
     //declaring non-abstract method  
     public int AddThreeNumbers(int a, int b, int c)  
     {  
       return a + b + c;  
     }  
   }  
   class DerivedClass : AbsClass // derived class inheriting the abstract class  
   {  
     // overring the abstract methods in derived class   
     public override int MultiplyTwoNumbers(int x, int y)  
     {  
       return x * y;  
     }  
     public override int SubtractTwoNumbers(int p, int q)  
     {  
       return p - q;  
     }  
   }  
   class Program  
   {  
     static void Main()  
     {  
       DerivedClass obj = new DerivedClass(); //created an object of derived class  
       // calling all methods with signatures   
       int Addition = obj.AddThreeNumbers(10, 20, 50);   
       int Multipication = obj.MultiplyTwoNumbers(3, 50);  
       int Substraction = obj.SubtractTwoNumbers(10, 3);  
       // not printing the output  
       Console.Write("Addition of 10, 20 and 50 = " + Addition.ToString() + "\n" + "Mutiplication of 3 and 50 = " + Multipication.ToString() + "\n" + "Subtraction of 10 and 3 = " + Substraction.ToString());  
       Console.ReadLine();  
     }  
   }  
 }  

Output :

I hope it would be helpful.
Thanks

Use of self join in SQL

Suppose we have an Employee table. All the employees  and their supervisor (or Manager) are in the same table. If we want to fetch the employees and their supervisors, how we could get them?

For that you need to choose self join. Self join is an inner join but only applied with one table. For applying self join we need 2 instance of the same table in memory. We have to use alias for making identical copies of the table because there is only one table.

See the example below.
Lets create a employee table first.

CREATE table mtblEmployee
(Employee_Id int identity (1,1),
Employee_Name nvarchar(50),
Supervisor_Id int
)

Now insert some value in this table

INSERT INTO mtblEmployee
SELECT 'Amit Mittal',0
UNION ALL 
SELECT 'Piyush Sharma',3
UNION ALL 
SELECT 'Dinesh Aggarwal',6
UNION ALL
SELECT 'Shobhit Roy',5
UNION ALL
SELECT 'Varun Dhiman',1

fetch the records

select * from mtblEmployee

By using self join we can fetch the employee and their supervisors

The below both query will give the same result

SELECT a.Employee_Id as Id, a.Employee_Name as Employee,
b.Employee_Name as Supervisor
FROM mtblEmployee a 
INNER JOIN  mtblEmployee b
on a.Supervisor_Id=b.Employee_Id

OR

SELECT a.Employee_Id as Id, a.Employee_Name as Employee,
b.Employee_Name as Supervisor 
FROM mtblEmployee a, mtblEmployee b 
where a.Supervisor_Id=b.Employee_Id

Result:

I hope it would help.

Thanks