Over Clause

/*******************************/
DROP TABLE #TEMP

CREATE TABLE #TEMP
(
Emp_Name varchar(100),
Age int,
Department Varchar(100),
Salary_Month datetime,
Salary money
)

INSERT INTO #TEMP
SELECT 'Anand',25,'Software','01/01/2013',2000
UNION
SELECT 'Anand',25,'Software','02/01/2013',2000
UNION
SELECT 'Anand',25,'Software','03/01/2013',2000
UNION
SELECT 'Anand',25,'Software','04/01/2013',2000
UNION
SELECT 'Anand',25,'Software','05/01/2013',2000
UNION
SELECT 'Anand',25,'Software','06/01/2013',2000
UNION
SELECT 'Vicky',28,'Software','01/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','03/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','04/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','05/01/2013',2300
UNION
SELECT 'Vicky',28,'Software','06/01/2013',2300
UNION
SELECT 'AS',26,'Software','01/01/2013',2500
UNION
SELECT 'AS',26,'Software','02/01/2013',2500
UNION
SELECT 'AS',26,'Software','03/01/2013',2500
UNION
SELECT 'AS',26,'Software','04/01/2013',2500
UNION
SELECT 'AS',26,'Software','06/01/2013',2500
UNION
SELECT 'BS',26,'Software','01/01/2013',3100
UNION
SELECT 'BS',26,'Software','02/01/2013',3100
UNION
SELECT 'BS',26,'Software','04/01/2013',3100
UNION
SELECT 'BS',26,'Software','05/01/2013',3100
UNION
SELECT 'BS',26,'Software','06/01/2013',3100
union
SELECT 'Naresh',26,'Billing','01/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','02/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','04/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','05/01/2013',2100
UNION
SELECT 'Naresh',26,'Billing','06/01/2013',2100
union
SELECT 'Prabhu',26,'Billing','01/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','02/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','04/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','05/01/2013',1800
UNION
SELECT 'Prabhu',26,'Billing','06/01/2013',1800


/*******************************/

/*Points to Remember*/

/**Over Clause is used to partition or order the given set of rows**/

/**Over Clause Always used with Aggregate / ranking functions**/

/**What is aggregate and Ranking? Which function belongs to Partition or Order?**/

/*Order By is related to Ranking function*/

/*Partition is related to Aggregate function*/

/*Now Come to Over Clause.. You can use any of the aggregate/ranking function with Over Clause

Syntax:

<<Aggregate/Ranking function>> OVER (<<Partition By/Order By>> ColumnName)

We can do lot of magics using Over Clause.

Check Below,

*/
/***************************************************************************************************************/

/*1.First, we can generate serial number (Row number) in select list*/

/* For this use Row number function in over clause*/
SELECT ROW_NUMBER() OVER (ORDER BY EMP_NAME) as row_number,* FROM #TEMP
/*Here row number is listed based on emp_name ascending order*/

SELECT ROW_NUMBER() OVER (ORDER BY EMP_NAME desc)  as row_number,* FROM #TEMP
/*Here row number is listed based on emp_name descending order*/

/*Like this we can generate row number by multible order by combination*/

SELECT ROW_NUMBER() OVER (ORDER BY Salary desc,EMP_NAME desc) as row_number,* FROM #TEMP

/***************************************************************************************************************/

/*2. We can get Rank based on particular values*/

SELECT RANK() OVER (ORDER BY salary desc) as Rank,* FROM #TEMP

/*We can get rank even by Age :-) */

SELECT RANK() OVER (ORDER BY age desc) as Rank,* FROM #TEMP

/*3. We can get Dense Rank based on particular values*/

SELECT dense_RANK() OVER (ORDER BY salary desc) as Rank,* FROM #TEMP

/*Find out the difference of dense rank and rank from both the select list*/


/***************************************************************************************************************/

/*4. NTILE -- It will split the row set into specific number of groups based on specific column*/
SELECT NTILE(4) OVER (ORDER BY salary desc) as NTILE,* FROM #TEMP

/*Here, total 31 rows splitted into 4 groups, 8 + 8 + 8 + 7 and Numbered 1,2,3,4 based on salary column
use this feature right place in ur project
*/

/*Yes.. We finished Ranking function with over clause*/

/*Now Aggregate functions.. Sum, Min, Max,count, etc..*/

/*Remember, here you have to use, partition by in over clause*/

/*****************************************************************************/

/*6.max*/
SELECT DISTINCT COUNT(SALARY_MONTH) OVER (PARTITION BY DEPARTMENT) AS CNT,DEPARTMENT FROM #TEMP
/*Here we are getting, number of salries given by each department*/

SELECT distinct max(salary) OVER (PARTITION BY EMP_NAME),EMP_NAME,DEPARTMENT  FROM #TEMP
/*Here we are getting, maximum salry got by each employee*/

SELECT distinct min(salary) OVER (PARTITION BY DEPARTMENT),DEPARTMENT,EMP_NAME  FROM #TEMP

/*Here we are getting, minimum salry given by department to employee*/


/*

There is lot of real time usage is there in Over Clause. Try to find out this feature in reight place of your project.

All the best.. Thank You! :-)

**/