Introduction
The purpose of this blog is to provide an answer to the below two questions
- Can we use OFFSET without FETCH in T-SQL?
- How can we skip some rows while returning a resultset through SQL query?
The above questions were asked multiple times to me by different people and in different forums. I’m publishing this blog to give a combined answer to both these questions
Illustration
Let me explain this with an illustration
The scenario under consideration is like this
Consider the case where we have payments made by customers as per invoices provided in a retail scenario. The same customer can do multiple payments in a month
This can be represented using a table with the below structure
declare @t table
(
Customer varchar(10),
Price decimal(15,2),
PaidDate date
)
insert @t
values
(‘Cust1′,1000,’20150112’),
(‘Cust3′,300,’20150120’),
(‘Cust2′,2300,’20150115’),
(‘Cust1′,1600,’20150322’),
(‘Cust3′,1800,’20150512’),
(‘Cust1′,1100,’20150610’),
(‘Cust2′,2450,’20150622’),
(‘Cust5′,900,’20150618’),
(‘Cust6′,340,’20150812’),
(‘Cust4′,570,’20150823’),
(‘Cust3′,800,’20151112’),
(‘Cust2′,1200,’20151125’),
(‘Cust4′,3200,’20160119’),
(‘Cust5′,4200,’20160123’),
(‘Cust3′,5300,’20160111’),
(‘Cust6′,2100,’20160203’)
The above data represents the various payments made by customers along with the date of payment information
The requirement is to get the top 3 customers from this based on the total payments done. In addition we also need to get the total payment done by all the other customers. This is for listing the details in a report
Now lets see how the above requirement can be achieved by using the OFFSET.. FETCH syntax which is available in T-SQL from SQL 2012 onwards.
We will need to break the requirements into two separate queries – one to get the top 3 customers along with their total payments and the second one to get totals for all the excluded customers.
We would need to aggregate the data based on Customer values so as to get total payments for them. For this purpose we will use a CTE as below
;With CTE
AS
(
SELECT Customer,SUM(Price) AS Tot
FROM @t
GROUP BY Customer
)
SELECT *
FROM CTE
This will give us the aggregated payment value for each customer
Now lets use this CTE and see how we can achieve our requirement. As suggested above we would require two queries for this
Frist query would look like below and it will give us the top 3 customers based on their total payment
SELECT Customer,Tot
FROM CTE
ORDER BY Tot DESC
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY
This query uses the default OFFSET..FETCH syntax to get the top 3 customers.
Now lets see how we can do to get the other customers.
We can achieve this requirement using the same OFFSET syntax without using the FETCH part. People seldom realize the fact that FETCH part is optional.
Using just a OFFSET with ORDER BY causes the resultset to exclude the number of rows as specified by OFFSET clause. This is what we would utilize here to get the details for the excluded customers.
It will look like the below
SELECT Customer,Tot
FROM CTE
ORDER BY Tot DESC
OFFSET 3 ROWS
Now if you aggregate the total from the above query it will give us total for all the other customers
Merging both the queries we can write final query as
SELECT *
FROM
(
SELECT Customer,Tot
FROM CTE
ORDER BY Tot DESC
OFFSET 0 ROWS FETCH FIRST 3 ROWS ONLY
)t
UNION ALL
SELECT ‘Others’,SUM(Tot)
FROM
(
SELECT Customer,Tot
FROM CTE
ORDER BY Tot DESC
OFFSET 3 ROWS
)t
Execute this and check the output to ensure it gives exactly what we want
Conclusion
As seen from the illustration above we can see that we get the expected result using the query.
This confirms two things
This confirms two things
1. OFFSET can be used without FETCH clause
2. We can use the OFFSET logic to skip a definite number of rows from our resultset as shown by the second query
