This is the third post in the series of posts on ‘ ‘How to Write Efficient SQL Queries and Statements’

For our demonstration, we will be working on the AdventureWorksLT17 database which is a fictitious, multinational manufacturing company called ‘Adventure Works Cycles’ used for OLTP (Online Transaction Processing) and Real-Time Analytics.

Advanced Data Aggregations

##Window Functions

Window Functions are those functions that are applied to a window or set of rows. They in turn include Ranking, Offset, Aggregate and Distribution functions.

Ranking Functions

**RANK** — Returns the Rank of Each Row within Partition of Results

SELECT TOP(5) SalesOrderID, CustomerID, TaxAmt,
RANK() OVER(ORDER BY TaxAmt DESC) AS RankbyTaxAmt
FROM SalesLT.SalesOrderHeader
ORDER BY RankbyTaxAmt;

Using the ‘RANK’ Function

**DENSE_RANK** — Similar to RANK, except it returns results without gaps, i.e. results that have consecutive rank values.

SELECT SalesOrderID, ProductID, OrderQty,
DENSE_RANK() OVER (PARTITION BY OrderQty
ORDER BY UnitPrice DESC) AS Rank
FROM SalesLT.SalesOrderDetail
ORDER BY OrderQty DESC;

#sql-server #sql-queries