Write Query to get 'x' number of rows in SQL Server

Question

I have two tables - Orders and Products. Each order can have 0 or more products.

--drop table orders
--drop table products
CREATE TABLE Orders (
 OrderId INT PRIMARY KEY,
 OrderDate DATE,
 CustomerName VARCHAR(50)
);
CREATE TABLE Products (
 ProductId INT PRIMARY KEY,
 ProductName VARCHAR(50),
 Price DECIMAL(10, 2),
 OrderId INT,
 FOREIGN KEY (OrderId) REFERENCES Orders(OrderId)
);
INSERT INTO Orders (OrderId, OrderDate, CustomerName)
VALUES (1, '2023-01-01', 'John Doe'),
 (2, '2023-01-02', 'Jane Smith'),
 (3, '2023-01-03', 'Michael Johnson'),
 (4, '2023-01-04', 'Emily Davis'),
 (5, '2023-01-05', 'Robert Wilson'),
 (6, '2023-01-06', 'Olivia Thompson'),
 (7, '2023-01-07', 'William Anderson'),
 (8, '2023-01-08', 'Sophia Martinez'),
 (9, '2023-01-09', 'James Hernandez'),
 (10, '2023-01-10', 'Ava Lee'),
 (11, '2023-01-11', 'Benjamin Walker'),
 (12, '2023-01-12', 'Mia Lewis'),
 (13, '2023-01-13', 'Ethan Hall'),
 (14, '2023-01-14', 'Charlotte White'),
 (15, '2023-01-15', 'Alexander King'),
 (16, '2023-01-16', 'Amelia Scott'),
 (17, '2023-01-17', 'Daniel Green'),
 (18, '2023-01-18', 'Harper Adams'),
 (19, '2023-01-19', 'Matthew Clark'),
 (20, '2023-01-20', 'Luna Rodriguez'),
 (21, '2023-01-21', 'Henry Carter'),
 (22, '2023-01-22', 'Lily Hill'),
 (23, '2023-01-23', 'Sebastian Turner'),
 (24, '2023-01-24', 'Avery Ward'),
 (25, '2023-01-25', 'Scarlett Foster'),
 (26, '2023-01-26', 'Joseph Diaz'),
 (27, '2023-01-27', 'Victoria Morris'),
 (28, '2023-01-28', 'David Sullivan'),
 (29, '2023-01-29', 'Penelope Price'),
 (30, '2023-01-30', 'Jackson Brooks');
INSERT INTO Products (ProductId, ProductName, Price, OrderId)
VALUES 
 -- Products for OrderId 1
 (1, 'Product A', 10.99, 1),
 (2, 'Product B', 19.99, 1),
 
 -- Products for OrderId 2
 (3, 'Product C', 5.99, 2),
 
 -- Products for OrderId 3
 (4, 'Product D', 7.50, 3),
 (5, 'Product E', 14.99, 3),
 (6, 'Product F', 8.99, 3),
 
 -- Products for OrderId 4
 (7, 'Product G', 12.99, 4),
 (8, 'Product H', 9.99, 4),
 (9, 'Product I', 6.49, 4),
 
 -- Products for OrderId 5
 (10, 'Product J', 18.50, 5),
 (11, 'Product K', 11.99, 5),
 
 -- Products for OrderId 6
 (12, 'Product L', 7.99, 6),
 (13, 'Product M', 13.99, 6),
 
 -- Products for OrderId 7
 (14, 'Product N', 9.99, 7),
 
 -- Products for OrderId 8
 (15, 'Product O', 16.99, 8),
 (16, 'Product P', 14.50, 8),
 
 -- Products for OrderId 9
 (17, 'Product Q', 8.99, 9),
 (18, 'Product R', 6.99, 9),
 
 -- Products for OrderId 10
 (19, 'Product S', 12.99, 10),
 
 -- Products for OrderId 11
 (20, 'Product T', 7.50, 11),
 
 -- Products for OrderId 12
 (21, 'Product U', 9.99, 12),
 (22, 'Product V', 11.99, 12),
 
 -- Products for OrderId 13
 (23, 'Product W', 15.99, 13),
 (24, 'Product X', 8.50, 13),
 
 -- Products for OrderId 14
 (25, 'Product Y', 9.99, 14),
 
 -- Products for OrderId 15
 (26, 'Product Z', 12.99, 15),
 (27, 'Product AA', 7.99, 15),
 (28, 'Product BB', 14.99, 15),
 
 -- Products for OrderId 16
 (29, 'Product CC', 11.50, 16),
 (30, 'Product DD', 9.99, 16);

Requirement: Write a query that filters the customerName column from the Orders table or ProductName column from the Products table based on the filter passed in the query and orders it by OrderDate and fetch the number of orders that are requested. Here's the query I wrote but it doesn't accomplish what i'm looking for:

declare @filter varchar(100) = 't'
declare @skiprows int = 5
declare @limitrows int = 5
select *
 from dbo.orders o
 left join dbo.products p
 on o.orderId = p.orderId
 where (customerName like '%'+@filter+'%'
 or productName like '%'+@filter+'%')
 order by orderdate desc
 offset @skiprows rows
 fetch next @limitrows rows only

Result: enter image description here

The parameters @limitrows and @skiprows are used to determine the desired result set. However, in the given query, skipping the first 5 rows and returning the next 5 rows does not provide the expected outcome. The goal is to skip the first 5 distinct Order Ids and then select the next 5 distinct Order Ids. Subsequently, the result set should be joined with the Products table to retrieve all corresponding products. It is important to note that the final result set may contain more than 5 rows since each order can have multiple products. If anyone could provide guidance on how to accomplish this, it would be greatly appreciated. Thank you!

Answer 1

One way would be to use DENSE_RANK

declare @filter varchar(100) = 't'
declare @skiprows int = 5
declare @limitrows int = 5;
WITH T AS
(
select o.*, 
 p.ProductId,
 p.productName,
 p.Price,
 Rnk = DENSE_RANK() OVER (ORDER BY o.orderdate desc, o.orderId DESC)
 from dbo.orders o
 left join dbo.products p
 on o.orderId = p.orderId
 where (o.customerName like '%'+@filter+'%'
 or p.productName like '%'+@filter+'%')
)
SELECT *
FROM T
WHERE Rnk > @skiprows AND Rnk <= @skiprows + @limitrows
order by Rnk

• Charlieface: Annoying you can't do OFFSET WITH TIES

Answer 2

DECLARE @filter VARCHAR(100) = 't';
DECLARE @skiprows INT = 2;
DECLARE @limitrows INT = 8;
SELECT *
FROM (
 SELECT *
 FROM orders
 WHERE (
 CustomerName LIKE '%' + @filter + '%'
 OR
 EXISTS
 (
 SELECT *
 FROM products
 WHERE products.OrderId = orders.OrderId
 AND ProductName LIKE '%' + @filter + '%'
 ))
 ORDER BY
 orderDate DESC, orderId DESC
 OFFSET @skiprows ROWS
 FETCH NEXT
 @limitrows ROWS ONLY
 ) orders
OUTER APPLY
 (
 SELECT *
 FROM products
 WHERE products.OrderId = orders.OrderId
 ) products
GO

It's not completely clear what exactly "based on the filter passed in the query" means in your question.

The query above finds all orders that have the letter "t" in either the customer's name or one of the products' names.

Out of this list, it takes the orders from the third last to the tenth last, and returns these orders and all their products (even those that don't have the letter "t" in the name). In your example, though, all the products have a letter "t" in them as a part of the word "product".

If an order doesn't have any products, the query will return a single record for this order with nulls in the product fields (similar to how left join works).

SQLFiddle