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

This blog will demonstrate how to get Max 2 UnitPrice from [Order details] for each ShipCountry in the Northwind database. Moreover, this blog will also make you comprehend the use of OFFSET and FETCH.

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

select distinct UnitPrice,ShipCountry from (select o.shipcountry,od.UnitPrice from orders o,[order details] od where o.orderid=od.orderid) as Table1
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
where Table1.shipcountry=Table2.shipcountry and Table2.UnitPrice>=Table1.UnitPrice)

In a more understandable format,

declare @table table (ShipCountry varchar(50),UnitPrice decimal(18,2))
Insert into @table select o.shipcountry,od.UnitPrice from orders o,[order details] od where o.orderid=od.orderid

select distinct UnitPrice,ShipCountry from @table t1
where 2>=(select count(distinct t2.UnitPrice) from @table t2 where t2.shipcountry=t1.shipcountry
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, 

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, 

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 :)