https://www.youtube.com/watch?v=AjSAvKdbJdI

Similar to: - Highest and Lowest Salaried emp in each dept.

DROP TABLE IF EXISTS Products;
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Product VARCHAR(255),
    Category VARCHAR(100)
);

INSERT INTO Products (ProductID, Product, Category)
VALUES
    (1, 'Laptop', 'Electronics'),
    (2, 'Smartphone', 'Electronics'),
    (3, 'Tablet', 'Electronics'),
    (4, 'Headphones', 'Accessories'),
    (5, 'Smartwatch', 'Accessories'),
    (6, 'Keyboard', 'Accessories'),
    (7, 'Mouse', 'Accessories'),
    (8, 'Monitor', 'Accessories'),
    (9, 'Printer', 'Electronics');

WITH
ROW_NUMBER()

with cte As (
SELECT * 
,row_number() OVER (partition by category order by productid) AS rn_asc
,row_number() OVER (partition by category order by productid desc) AS rn_desc
from products
)
SELECT ac.productid,dc.product,dc.category 
from cte ac join cte dc on ac.rn_asc = dc.rn_desc and ac.category = dc.category
order by productid