ROW_NUMBER() Function

The Row_Number() function is an important function when you do paging in SQL Server. The Row_Number() function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.

ROW_NUMBER() Function without Partition By clause

Partition by clause is an optional part of Row_Number() function and if you don't use it all the records of the result set will be considered as a part of single record group or a single partition and then ranking functions are applied.

SELECT

ROW_NUMBER() OVER (ORDER BY SalesGroup) RowNum,

SalesGroup,

Country,

AnnualSales

FROM RegionalSales

ROW_NUMBER() Function with Partition By clause

When you specify a column or set of columns with the PARTITION BY clause, then it will divide the result set into record partitions. Then, finally ranking functions are applied to each record partition separately, and the rank will restart from 1 for each record partition separately.

SELECT

ROW_NUMBER() OVER (ORDER BY SalesGroup) GroupRow,

SalesGroup,

ROW_NUMBER() OVER (PARTITION BY SalesGroup ORDER BY SalesGroup) ContryRow,

Country,

AnnualSales

FROM RegionalSales

ORDER BY GroupRow

RANK() Function

The SQL rank() analytic function is used to get rank of the rows in column or within group. The Rows with equal or similar values receive the same rank with next rank value skipped. The rank analytic function is usually used in top n analysis.

SELECT RANK() OVER (ORDER BY SalesGroup) SalesGrpRank,

SalesGroup,

RANK() OVER (Partition by SalesGroup ORDER BY Country) CountryRank,

Country,

AnnualSales

FROM RegionalSales

DENSE_RANK() Function

The SQL dense_rank() analytic function returns the rank of a value in a group. Rows with the equal values for ranking criteria receive the same rank and assign rank in sequential order i.e. no rank values are skipped. The dense_rank analytic function is also used in top n analysis.

SELECT

DENSE_RANK() OVER (ORDER BY SalesGroup) SG_DenseRank,

RANK() OVER (ORDER BY SalesGroup) SG_Rank,

SalesGroup,

RANK() OVER (Partition by SalesGroup ORDER BY Country) CountryRank,

Country, AnnualSales

FROM RegionalSales

NTILE() function

The SQL Server NTILE() is a window function that distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.

SELECT

DATENAME(yyyy,HireDate) HireYear,

COUNT(hiredate) HireCount,

NTILE(4) over (order by DATENAME(yyyy,HireDate)) TimePeriod

FROM [HumanResources].[Employee]

GROUP BY DATENAME(yyyy,hiredate)

WITH Insight AS (

SELECT CASE MONTH(HireDate)

when 1 then 'JAN'

when 2 then 'FEB'

when 3 then 'MAR'

when 4 then 'APR'

when 5 then 'MAY'

when 6 then 'JUN'

when 7 then 'JUL'

when 8 then 'AUG'

when 9 then 'SEP'

when 10 then 'OCT'

when 11 then 'NOV'

when 12 then 'DEC'

END as MonthNum

, COUNT(HireDate) HireByMonth

FROM [HumanResources].[Employee]

GROUP BY MONTH(HireDate)

)

SELECT

MonthNum,

HireByMonth,

NTILE(3) OVER (Order by HireByMonth DESC) TimePeriod

FROM Insight

ORDER BY HireByMonth DESC

Ranking functions together with some basic aggregate functions:

SELECT

DENSE_RANK() OVER (ORDER BY SalesGroup) SG_DenseRank,

SalesGroup,

RANK() OVER (Partition by SalesGroup ORDER BY Country) CountryRank,

Country, AnnualSales,

sum(AnnualSales) OVER(PARTITION BY SalesGroup ) TotalByGroup,

AVG(AnnualSales) OVER(PARTITION BY SalesGroup ) AVGByGroup,

MIN(AnnualSales) OVER(PARTITION BY SalesGroup ) MINByGroup,

MAX(AnnualSales) OVER(PARTITION BY SalesGroup ) MAXByGroup

FROM RegionalSales

SELECT

Concat_WS(': ' ,SalesGroup,Format(SUM(AnnualSales),'c0')) Total_ByGroup,

Concat_WS(': ' ,SalesGroup,Format(AVG(AnnualSales),'c0')) AVG_ByGroup,

Concat_WS(': ' ,SalesGroup,Format(MIN(AnnualSales),'c0')) MIN_ByGroup,

Concat_WS(': ' ,SalesGroup,Format(MAX(AnnualSales),'c0')) MAX_ByGroup

FROM RegionalSales

group by SalesGroup

Displaying running total. A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, by adding the value of the new number to the previous running total. Another term for it is partial sum. This example displays running totals within a partition.

SELECT SalesGroup,Country,

RANK() OVER (PARTITION BY SalesGroup ORDER BY country)'Sequence Number',

SUM(AnnualSales) OVER (PARTITION BY SalesGroup ORDER BY country) 'Running Total'

FROM RegionalSales

ORDER BY SalesGroup,country

Running total with subquery for the entire data set:

SELECT SalesGroup, Country,rs.Annualsales,

(SELECT SUM(AnnualSales) FROM RegionalSales

WHERE SalesID <= rs.SalesID)

'Runnig Total'

FROM RegionalSales rs

Running total using self join:

SELECT rs1.SalesGroup, rs1.Country,rs1.Annualsales,

Sum(rs2.AnnualSales) 'Runnig Total'

FROM RegionalSales rs1

JOIN RegionalSales rs2

ON rs1.SalesID <= rs2.SalesID

GROUP BY rs1.SalesGroup, rs1.Country,rs1.Annualsales

ORDER BY Sum(rs2.AnnualSales)

OVER clause in SUM()

I want to calculate a percentage share for individual employees from Northwind database.

First I create a code that calculate the revenue for the employees by joining 3 tables.

Then I use SUM(Revenue) OVER () clause to create a column containing Total Revenue which I have to divide by individual revenues to get the Percentage share back

code:

Select *,

SUM(Revenue)over () TotalReveue,

(Revenue/SUM(Revenue) over()) Perc_Share

from

(

Select [LastName], [FirstName], [Title], [TitleOfCourtesy], [City], [Country],

Sum((UnitPrice*(1-Discount))*Quantity) Revenue from Employees e

Inner join Orders o

on e.EmployeeID= o.EmployeeID

inner join [Order Details] od

ON O.OrderID = oD.OrderID

Group by [LastName], [FirstName], [Title], [TitleOfCourtesy], [City], [Country]

)Result

Group by [LastName], [FirstName], [Title], [TitleOfCourtesy], [City], [Country],Revenue

Order by Perc_Share desc

## Comments