Window Functions in SQL | ROW NUMBER | RANK | DENSE RANK | SQL

-- just an example to understand window functions create table wind (dept varchar(10), salary int, number int) insert into wind values ('A', 10000, 11), ('A', 10000,12), ('A', 10000,13), ('A', 12001,14), ('A', 12001,15), ('B', 15001,15) , ('B', 16001,16), ('B', 15001,16), ('C', 20000, 19), ('C', 20000, 21), ('C', 18000, 22), ('C', 19000, 23), ('C', 21000, 24) select * from wind select dept, salary, rank() over(partition by dept order by salary) as rank -- dense_rank() over(partition by dept order by salary) as drank, -- row_number() over (partition by dept order by salary) as row_no from wind -- second highest earning member of each dept with cte as ( select dept, salary, rank() over(partition by dept order by salary desc) as rank, dense_rank() over(partition by dept order by salary desc) as drank -- row_number() over (partition by dept order by salary) as row_no from wind ) select * from cte where drank = 2 -- select highest earning member of each department with cte as ( select *, rank() over (partition by dept order by salary desc) as rn from wind ) select * from cte where rn = 1 -- select the person with 2nd highest salary in each dept ; with cte as ( select *, row_number() over (partition by dept order by salary desc) as rn ,rank() over (partition by dept order by salary desc) as rank , dense_rank() over (partition by dept order by salary desc) as drank from wind ) select * from cte where drank = 2 -- partition by clause is optional -- in below case all the rows will be considered into 1 partition only. select *, rank() over(order by salary) as rank, dense_rank() over(order by salary) as drank, row_number() over (order by salary) as rno from wind