nth Highest Salary using window function in sql server

By
A very common question which you will hear in interviews.

How to find nth highest salary?

Let's do it:
The first step, create a table

create table #Employee(id int, name varchar(15), salary int);

Insert Dummy Data

insert into #Employee(id,name,salary) values(1,'A',100)
insert into #Employee(id,name,salary) values(2,'B',102)
insert into #Employee(id,name,salary) values(3,'C',106)
insert into #Employee(id,name,salary) values(4,'D',102)
insert into #Employee(id,name,salary) values(5,'E',108)
insert into #Employee(id,name,salary) values(6,'F',110)
insert into #Employee(id,name,salary) values(7,'G',117)

Let's see what we have in the table

select * from #Employee;


Query to find 3rd Highest Salary using row_number()

select a.id,a.name,a.salary from (
select e.id,e.name,e.salary, row_number() over(order by salary) as rn  from #Employee e) as a where rn=3;

output: 



Comments

Post a Comment

Popular posts from this blog

PostgreSQL PSQL Commands

By
To connect to postgresql using psql, update port, dbname, user and your_password in cmd below psql "host=localhost port=5432 dbname={your_database} user=user password={your_password} sslmode=require" To import data dump from .sql file using psql, use following cmd psql > \i 'path-to-dump.sql' Having single quote around the path is important else you may see C:: permission denied error. If using sudo  sudo -u postgres psql db_name < 'file_path' Create user  postgres=# create user user_name password 'password'; Create database  postgres=# create database mydb owner user_name ; Install PostgreSQL Client on Ubuntu bash:=# apt install postgresql-client- ; To Quit bash:=# \q ; Help bash:=# \h ; To describe attributes of a table. bash:=# \d tablename ; To get list of databases. bash:=# \l ; To connect to a database. bash:=# \c databasename ; To list all details of a database. bash:=# \z or select * from pg_tables where s...
Read more

Delegates in C#

By
Let's start with a very basic definition of Delegates in the real world. Delegates in the real world refer to transferring the work/tasks assigned to self to someone else. A team leader, supervisor, the manager can delegate work to his subordinate. A  delegate in C#  is similar to a function pointer in C or C++. Using a  delegate  allows the programmer to encapsulate a reference to a method inside a  delegate  object. The  delegate  object can then be passed to code which can call the referenced method, without having to know at compile time which method will be invoked. When to use Delegate : Source: https://msdn.microsoft.com/en-us/library/ms173173.aspx Use a delegate in the following circumstances: An eventing design pattern is used. It is desirable to encapsulate a static method. The caller has no need to access other properties, methods, or interfaces on the object implementing the method. An easy composition is des...
Read more