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

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,

e.g.

Let's take a table called Employee with following details.

Execute query,

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.

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.

Hope this may help you :)