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