The PERCENT_RANK function calculates the ranking of a row relative to the row set.

The percentage is based on the number of rows in the group that have a lower value

than the current row.

The first value in the result set always has a percent rank of zero.

The value for the highest-ranked – or last – value in the set is always one.

The CUME_DIST function calculates the relative position of a specified value in a group

of values, by determining the percentage of values less than or equal to that value.

This is called the cumulative distribution.

For SQL Server, this function calculates the cumulative distribution of a value within a group of
values.

In other words, CUME_DIST calculates the relative position of a specified value in a group of values.

Assuming ascending ordering, the CUME_DIST of a value in row r is defined as the number of rows
with values

less than or equal to that value in row r, divided by the number of rows evaluated in the partition or
query result set.

The CUME_DIST() OVER ( [ partition_by_clause ] order_by_clause)

SELECT *, 100*CUME_DIST() OVER(ORDER BY SAL ASC) FROM EMP1

Calculates the relative rank of a row within a group of rows in SQL Server.

Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition.

PERCENT_RANK is similar to the CUME_DIST function.

PERCENT_RANK( )

OVER ( [ partition_by_clause ] order_by_clause )

SELECT *, 100* PERCENT_RANK( ) OVER(ORDER BY SAL ASC) FROM EMP1

PERCENTILE_CONT

PERCENTILE_CONT ( numeric_literal )

WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )

OVER ( [ <partition_by_clause> ] )

Calculates a percentile based on a continuous distribution of the column value in SQL Server.

The result is interpolated and might not be equal to any of the specific values in the column.

SELECT SAL, 100*PERCENT_RANK() OVER(ORDER BY SAL ASC), PERCENTILE_CONT(0.5

) WITHIN GROUP(ORDER BY SAL) OVER() FROM EMP1

PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ]

)

OVER ( [ <partition_by_clause> ] )

PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values.

Computes a specific percentile for sorted values in an entire rowset or within a rowset's distinct
partitions in SQL Server. For a given percentile value P, PERCENTILE_DISC sorts the expression values
in the ORDER BY clause. It then returns the value with the smallest CUME_DIST value given (with
respect to the same sort specification) that is greater than or equal to P. For example,
PERCENTILE_DISC (0.5) will compute the 50th percentile (that is, the median) of an expression.
PERCENTILE_DISC calculates the percentile based on a discrete distribution of the column values.

The result is equal to a specific column value.The result is equal to a specific column value.

SELECT SAL, 100*PERCENT_RANK() OVER(ORDER BY SAL ASC), PERCENTILE_DISC(0.5

) WITHIN GROUP(ORDER BY SAL) OVER() FROM EMP1

AGGREGATE WINDOW FUNCTION:

THEY CAN BE USED TO AGGREGATE ROWS IN THE DEFINED WINDOW.

ALL 3 CLAUSES CAN BE USED BUT NOT MANDATORY -- IE PARTITION BY ORDER BY FRAME

====================================================================

SUM(SAL) OVER() --EXPOSES ALL ROWS --AND GIVES GRAND TOTAL OF ALL ROWS

SELECT *,SUM(SAL) OVER() FROM EMP1

==========================================
SUM(SAL) OVER(PARTITION BY DEPTNO) GIVES TOTAL VALUE FOR CURRENT DEPARTMENT

SELECT *,SUM(SAL) OVER(PARTITION BY DEPTNO) FROM EMP1

==============================================================

SUM(SAL) OVER(ORDER BY SAL) --- GIVES RUNNING TOTAL

SELECT *,SUM(SAL) OVER(ORDER BY SAL) FROM EMP1

SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

SELECT *,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP1

===================================================================

OFFSET WINDOW FUNCTIONS

UED TO RETURN AN ELEMENT FROM A ROW THAT IS AT A CERTAIN OFFSET FROM THE CURRENT

ROW OR AT THE BEGINING OR END OF WINDOW FRAME

LAG (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

LEAD ( scalar_expression [ ,offset ] , [ default ] )

OVER ( [ partition_by_clause ] order_by_clause )

*LAG AND LEAD SUPPORTS PARTITION BY AND ORDER BY BUT NOT NO WINDOWS FRAMING

ORDER BY MANDATORY

LAG FUNCTIONS LOOK BEFORE CURRENT ROW BASED ON INDICATED ORDERING

LEAD FUNCTIONS LOOK AFTER CURRENT ROW BASED ON INDICATED ORDERING

LAG(ELEMENT TO RETURN, [OFFSET],[DEFAULT VALUE]) ---> DEFAULT OFFSET IS 1

DEFAULT VALUE IS THE VALUE TO RETURN IF THE ROW AT THAT OFFSET NOT EXISTS

THE DEFAULT VALUE IS NULL

SELECT *, LAG(SAL) OVER(ORDER BY SAL DESC) FROM EMP1

SELECT *, LAG(SAL,2) OVER(ORDER BY SAL DESC) FROM EMP1

SELECT *,LAG(CAST(SAL AS VARCHAR),2, 'N/A') OVER(ORDER BY SAL DESC) FROM EMP1

SELECT *,LAG(CAST(SAL AS VARCHAR),2, 'N/A') OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)
FROM EMP1

FIRST_VALUE

FIRST_VALUE ( [scalar_expression ] )

OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ]

LAST_VALUE

THEY RETURN AN ELEMENT FROM FIRST AND LAST ROWS IN WINDOWS FRAME

RESPECTIVELY. THEY SUPPORT PARTITION BY , ORDER BY , WINDOWS FRAME

ORDER BY IS MANDATORY

SELECT *, FIRST_VALUE(SAL) OVER(ORDER BY SAL DESC) FROM EMP1

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY PRODUCT_CATEGORY ORDER BY

PRICE DESC) AS RN FROM PRODUCT) AS E WHERE RN=1