SQL Server Learning: Basic Query_ OFFSET-FETCH; LEAD AND LAG; RANK

Last Updated on August 15, 2022 by Sean Lee

SQL Server since version 2012/2014 has a new feature OFFSET and FETCH. The OFFSET and FETCH clause provides you an option to fetch only a page or a window of the results from the complete result set.
OFFSET: Specifies the number of rows to skip, before starting to return rows from the query
expression.

FETCH: Specifies the number of rows to return, after processing the OFFSET clause.

Note:

1. ORDER BY is mandatory to use OFFSET and FETCH clause
2. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH
3. TOP cannot be combined with OFFSET and FETCH in the same query expression

Example:

SELECT First Name + ‘ ‘ + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS;

SELECT First Name + ‘ ‘ + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Resource:

1. New T-SQL features in SQL Server 2012 – OFFSET and FETCH
   https://goo.gl/CJqhvk
2. OFFSET FETCH Clause
   https://goo.gl/rnEMvV
3. Overview of OFFSET and FETCH Feature of SQL Server 2012
   https://goo.gl/TqpbnK

Practice Question 1
Your database contains a table named Customer that has columns named CustomerID and Name.You want to write a query that retrieves data from the Customer table sorted by Name listing 20 rows at a time.

You need to view rows 41 through 60.

Which Transact-SQL query should you use?

š A. SELECT * FROM Customer ORDER BY Name FETCH ROWS BETWEEN 41 AND 60
š B. SELECT * FROM Customer ORDER BY Name OFFSET 40 ROWS FETCH NEXT 20 ROWS
ONLY
š C. SELECT TOP 20 * FROM Customer ORDER BY Name
š D. WITH Data AS (SELECT *, Rn = ROW_NUMBER() OVER(ORDER BY CustomerID,
Name) FROM Customer)
SELECT * FROM Data WHERE Data.Rn BETWEEN 40 AND 60

Answer & Explanation

Correct Answer: B
Explanation:
š A, Wrong  syntax  “FETCH ROWS BETWEEN 41 AND 60”).
ž B, Correct Skip first 40 row and fetch next 20 rows that is 41 through 60. 
š C, Wrong  get the top 20 rows, not between 41 and 60

š D, Wrong   get rows between 40 and 60, not the reqired 41 and 60

SQL Server 2012 introduces new analytical function LEAD() and LAG(). These functions accesses data from a subsequent row (for LEAD) and previous row (for LAG) in the same result set without the use of a self-join.

Syntax:

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

OVER ( [ partition_by_clause ] order_by_clause )

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

OVER ( [ partition_by_clause ] order_by_clause )

Parameters:

1. SCALAR_EXPRESSION: column name for which value needs to be accessed
2. OFFSET: nth previous (for LAG) or next (for LEAD) row to access the column
3. DEFAULT: default value to display if nth row does not exist

Example:

USE AdventureWorks2014;

GO

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota,

LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS '(LEAD)NextQuota',

LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS '(LAG)PreviousQuota'

FROM Sales.SalesPersonQuotaHistory

WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2012','2013');

Output:

    Resource:

1. LEAD (Transact-SQL)
   https://goo.gl/C6xONx
2. LAG (Transact-SQL)
   https://goo.gl/Ub4Zqu
3. Servers Lag and Lead Functions to Help Identify Date Differences
   https://goo.gl/9oaL6v

Practice Question 2
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.You need to create a report that displays the profits made by each territory for each year and its preceding year.

Which Transact-SQL query should you use?

š  A. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Year ORDER
BY Territory) AS NextProfit FROM Profits

š B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER(PARTITION BY Territory
ORDER BY Year) AS NextProfit FROM Profits

š C. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER(PARTITION BY Territory
ORDER BY Year) AS NextProfit FROM Profits

š D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER(PARTITION BY Year
ORDER BY Territory) AS NextProfit FROM Profits

Answer & Explanation

Correct Answer: B

Explanation:

š A, Wrong ‘preceding year’ required order by year not Territory.
ž B, Correct  LAG returns previous nth year data, also correct partion by Territory and order by year.  
š C, Wrong  LEAD() display next nth year data.
š D,Wrong  LEAD() display next nth year data.

SQL Server provides ranking functions with your result set. From MSDN “Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.”

In simple terms, ranking functions allow you to sequentially number your result set. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional.

Transact-SQL provides the following ranking functions:

RANK(): Returns the rank of each row in the result set of partitioned column.

Syntax:
RANK() OVER ( [ < partition_by_clause > ] < order_by_clause > )

DENSE_RANK(): This is same as RANK() function. Only difference is returns rank without gaps.

Syntax:
DENSE_RANK() OVER( [ ] < order_by_clause > )

NTILE(): Distributes the rows in an ordered partition into a specified number of groups. It divides the partitioned result set into specified number of groups in an order.

Syntax:
NTILE(integer_expression) OVER( [ ] < order_by_clause > )

ROW_NUMBER(): Returns the serial number of the row order by specified column.

Syntax:
ROW_NUMBER() OVER ( [ < partition_by_clause > ] < order_by_clause > )

Example:

select Name, Marks,

RANK() over(order by marks desc) as [Rank],

DENSE_RANK() over(order by marks desc) as [DenseRank],

NTILE(3) over(order by marks desc) as [nTile],

ROW_NUMBER() over(order by marks desc) as [RowNumber]

from Studentmarks

Output:

Resource:

1. Ranking Functions (Transact-SQL)
   https://goo.gl/x2MRek
2. SQL SERVER Ranking Functions – RANK, DENSE_RANK, NTILE, ROW_NUMBER
   https://goo.gl/KS3ojZ

Practice Question 3
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid- year marks for students. The table has marks obtained by 50 students for various subjects.You need to retrieve the students who scored the highest marks for each subject along with the marks.Which Transact-SQL query should you use?

A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM
StudentMarks

C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS
Value FROM StudentMarks
GROUP BY StudentCode

D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS
Value FROM StudentMarks
GROUP BY StudentCode

E. SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM
StudentMarks) tmp
WHERE Rank = 1

F. SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode
ORDER BY  Marks DESC) AS Rank FROM
StudentMarks) tmp
WHERE Rank = 1

G. SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM
StudentMarks) tmp
WHERE Rank = 1

H. SELECT StudentCode AS Code, Marks AS Value FROM (
SELECT StudentCode, Marks AS Marks,
RANK OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM
StudentMarks) tmp
WHERE Rank = 1

Answer & Explanation
Correct Answer: F
Explanation:

š A,  Wrong  Require list student and the highest mark of every subject, not the average mark

š B,  Wrong  Should be partitioned by subject

š C,  Wrong  average mark

š D,  Wrong  average mark

 E,  Wrong  Order by marks asc, from low to high

ž F,  Correct Rank over by marks DESC, partition by Subject, then select the highest rank 1

š G,  Wrong  Partition by studentcode, should by subject

š H,  Wrong  Partition by studentcode, should by subject