14
DecCyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
A SQL Server window function performs a calculation on a set of table rows that are somehow related to the current row. This is corresponding to the kind of calculation that may be completed with an aggregate function. But unlike normal aggregate functions, using a SQL Server window function does not result in the rows being grouped into a single output row - the rows keep their identities separate. Behind the scenes, the SQL Server window function can access more than the current row of query result.
In the Following Paragraphs We Will Discuss the Following Points
In the following paragraphs, we will describe the above mentioned SQL Windows function in detail with examples. We will be using the Adventureworks database for all of the examples below. You can download the database from the link below depending on the version of SQL Server you are using to practice the Windows SQL function.
CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is> 0 to andlt; = 1. The equilibrium values always return the same cumulative distribution value.
The syntax is as below
SELECT SalesOrderID, OrderQty, CUME_DIST() OVER(ORDER BY SalesOrderID) AS CDist FROM Sales.SalesOrderDetail WHERE SalesOrderID IN (43670, 43669, 43667, 43663) ORDER BY CDist DESC
The output is as below.
This returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks precedes the row in question.
The syntax is like this
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS p ON i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID;
The output is
This SQL Server windows function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is equal to one plus the number of distinct rank values preceding that specific row.
The syntax is like this
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS p ON i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID;
The output is
SELECT Department, LastName, Rate, CUME_DIST () OVER (PARTITION BY Department ORDER BY Rate) AS CumeDist, PERCENT_RANK() OVER (PARTITION BY Department ORDER BY Rate ) AS PctRank FROM HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN (N'Information Services',N'Document Control') ORDER BY Department, Rate DESC;
The output is
ROW_NUMBER Numbers the output of a result set. Specifically, it returns the sequential number of a row within a partition of a result set, starting at 1 for the first row of each partition.
The syntax is like this
SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id
The output is
This FIRST_VALUE function will return the First Value in each partition.
The syntax as below
SELECT SalesOrderID,CustomerID, YEAR(OrderDate)as[Year],TotalDue, FIRST_VALUE(CONVERT(DATE,OrderDate)) OVER (Partition BY Year(OrderDate) ORDER BY OrderDate) AS FirstOrderDate FROM Sales.SalesOrderHeader WHERE CustomerID=29994
The output is
We use a Lag() function to access previous rows data as per defined offset value.
The syntax is like this
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS PreviousQuota FROM Sales.SalesPersonQuotaHistory WHERE BusinessEntityID = 275
The output looks like below
This SQL Windows function returns the last value in an ordered set of values.
The syntax is a below.
SELECT Department, LastName, Rate, HireDate, LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue FROM HumanResources.vEmployeeDepartmentHistory AS edh INNER JOIN HumanResources.EmployeePayHistory AS eph ON eph.BusinessEntityID = edh.BusinessEntityID INNER JOIN HumanResources.Employee AS e ON e.BusinessEntityID = edh.BusinessEntityID WHERE Department IN (N'Information Services',N'Document Control');
The output is as below
LEAD() is a SQL window function that provides access to a row at a specified physical offset which follows the current row.
The syntax is like this
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,
LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275
NTILE() function in SQL Server is a window function that distributes rows of an ordered partition into a pre-defined number of roughly equal groups.
SELECT p.FirstName, p.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile
,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD
, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
The Output is as Below
The following table details the different types of windows functions available in SQL Server.
Name |
Description |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Practice makes a man perfect. For practice, it is always best to download and install the SQL Server Database Engine on your laptop or desktop computer. But if you are a tech savvy person and want to practice uncontrollable SQL queries whenever you are free, here are some online tools where you can try practicing SQL queries online.
Name |
Brief Description |
Link |
LearnSQL.com |
|
https://learnsql.com/ |
SQL Fidle |
|
http://sqlfiddle.com/ |
SQLZoo |
|
https://sqlzoo.net/ |
W3Resource |
|
https://www.w3resource.com/ |
Certificate |
Exam No |
Course |
MTA: Database Fundamentals |
98-364 |
|
Microsoft Azure Data Fundamentals |
DP-900 |
|
This particular article details the different types of Windows SQL functions and their use. It also provides information on how to practice them online and also how to use the skills acquired through learning Windows SQL function in Microsoft certifications. This is just a small step to familiarize you with the Windows SQL function. This will encourage you to venture deeper into the world of Windows SQL functions and learn more.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Interviews
Jax Williams
This is a very nice informative guide, really helpful!
JanbaskTraining
Glad you found this useful! For more such insights on your favourite topics, do check out JanBask Training Blogs and keep learning with us!
Amari Jones
I am learning SQL queries these days, I would definitely try these tools you have mentioned.
JanbaskTraining
Glad you found this useful! For more such insights on your favourite topics, do check out JanBask Training Blogs and keep learning with us!
Zane Brown
How to apply for this Microsoft Azure Data fundamental exam. Can you guide me on this? I would like to take this certification as I am into learning SQL server.
JanbaskTraining
Thank you so much for your comment, we appreciate your time. Keep coming back for more such informative insights. Cheers :)
Emilio Davis
hi, where can I apply for the SQL course? Can anyone from your team help me apply for it?
JanbaskTraining
Hello, JanBask Training offers online training to nurture your skills and make you ready for an amazing career run. Please write to us in detail at help@janbasktraining.com. Thanks!
Knox Miller
Nice information. Can you help me find some SQL server interview questions & answers often asked in the interviews.
JanbaskTraining
Glad you found this useful! For more such insights on your favourite topics, do check out JanBask Training Blogs and keep learning with us!