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