Post
Ask Question

How To Get Max 2 Record For Each Category From Table. What Is The Use Of Offset And Fetch In SQL Server

I have used the Northwind database. Here I am using two tables of Northwind DB - Orders [PK - OrderId] and OrderDetails [FK-OrderId].
 
Orders table columns - OrderId, CustomerId, OrderDate,...., ShipCountry
 
OrderDetails table columns - OrderID, ProductId, UnitPrice, Quantity, Discount
 
Question - Select Max 2 UnitPrices from each Country (ShipCountry here).
 
Query
  1. select distinct UnitPrice,ShipCountry from (select o.shipcountry,od.UnitPrice from orders o,[order details] od where o.orderid=od.orderid) as Table1    
  2. where 2>=(select count(distinct Table2.UnitPrice) from (select o.shipcountry,od.UnitPrice from orders o,[order details] od where o.orderid=od.orderid) as Table2    
  3. where Table1.shipcountry=Table2.shipcountry and Table2.UnitPrice>=Table1.UnitPrice)   
In a more understandable format,
  1. declare @table table (ShipCountry varchar(50),UnitPrice decimal(18,2))    
  2. Insert into @table select o.shipcountry,od.UnitPrice from orders o,[order details] od where o.orderid=od.orderid    
  3.     
  4. select distinct UnitPrice,ShipCountry from @table t1    
  5. where 2>=(select count(distinct t2.UnitPrice) from @table t2 where t2.shipcountry=t1.shipcountry    
  6. and t2.UnitPrice>=t1.UnitPrice)     
Now let's understand what is OFFSET and FETCH in SQL SERVER.
 
OFFSET and FETCH clause are used along with SELECT and ORDER BY to retrieve a range of records.
 

OFFSET

 
It skips the number of rows from the result returned by Select and Order By query.
 
Points to remember,
  1. OFFSET can only be used with Order By
  2. OFFSET value should be >=0.
e.g. 
 
Let's take a table called Employee with following details. 
 
 Name  Salary  Gender
 ABC  200000  Male
 XYZ  500000  Female
 CDE  300000  Male
 LMN  1200000  Male
 JKL  150000  Female
 
Execute query, 
  1. SELECT Name, Salary from Employee Order By Salary Offset 1 rows;  
The above query will return all the rows with Name & Salary ordered by Salary but will skip the first row after performing "order by" as shown below.
 
 Name  Salary  Gender
 ABC  200000  Male
 CDE  300000  Male
 XYZ  500000  Female
 LMN  1200000  Male
 

FETCH

 
It's used to return the range of rows after applying the OFFSET. So, its always used along with OFFSET.
 
Let's take the same Employee table and execute the below query, 
  1. SELECT Name, Salary from Employee Order By Salary Offset 2 rows Fetch Next 2 Rows only;  
The above query will return CDE and XYZ records as Fetch Next 2 rows; it will only retrieve the next 2 rows after the offset 2 rows.
 
Name  Salary  Gender
 CDE  300000  Male
 XYZ  500000  Female
 
Hope this may help you :)