Window Functions in SQL

SELECT coulmn_name1, 
window_function(column_name2),
OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;
window_function= any aggregate or ranking function
column_name1= column to be selected
coulmn_name2= column on which window function is to be applied
column_name3= column on whose basis partition of rows is to be done
new_column= Name of new column
table_name= Name of table
SELECT Name, Age, Department, Salary, 
AVERAGE(Salary) OVER( PARTITION BY Department ORDER BY Age) AS Avg_Salary
FROM employee
select e.*, max(salary) over(partition by dept_name) as max_salary
from employee e;
select e.*,
row_number() over(partition by dept_name) as rn
from employee e;
select * from (
select e.*,
row_number() over(partition by dept_name order by emp_id) as rn
from employee e) x
where x.rn < 3;
select * from (
select e.*,
rank() over(partition by dept_name order by salary desc) as rnk
from employee e) x
where x.rnk < 4;
select * from (
select e.*,
dense_rank() over(partition by dept_name order by salary desc) as rnk
from employee e) x
where x.rnk < 4;
select e.*,
lag(salary) over(partition by dept_name order by emp_id) as prev_empl_sal,
lead(salary) over(partition by dept_name order by emp_id) as next_empl_sal
from employee e;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store