SQL Server : SELECT query to get DISTINCT and MAX display order value

Question

I have a product table, Category table, and Mapping table. Category saved as a category tree. If a single product has mapped with the last category in a hierarchy of level three. All the levels saved in the mapping table with the same product id.

eg : Assume there is category tre like this Electronic>LapTops>DELL and when product id = 1 assigned to category 'DELL' mapping will save as [1,Electronic],[1,LapTops],[1,DELL]

When I get data with a select query all the category levels appear with the same product Id.

My problem is I need to retrieve data as [productId, ProductName, LastCategortLevel, CategoryName, CategoryId].

Refer actual result below. I just need to pick the highlighted product with the last category level which is the highest category order level.

I can't use another stored procedure or function because it's a small part of a large stored procedure.

The actual database tables are very big. But I have tried to implement the same scenario with small temp tables. see the below queries.

DECLARE @Products TABLE (ProductId INT NOT NULL)

INSERT INTO @Products(ProductId)  
    SELECT ProductId 
    FROM (VALUES (1), (2), (3), (4)) as x (ProductId)

DECLARE @Categories TABLE (CategoId INT NOT NULL,
                           Name VARCHAR(MAX) NOT NULL,
                           ParentCategoryId INT NOT NULL,
                           DisplayOrder INT NOT NULL)

-- 1st category tree
INSERT INTO @Categories VALUES (10, 'Electronic', 0, 1)
INSERT INTO @Categories VALUES (11, 'LapTops', 10, 2)
INSERT INTO @Categories VALUES (12, 'DELL', 11, 3)
INSERT INTO @Categories VALUES (13, 'HP', 11, 3)

-- 2st category tree
INSERT INTO @Categories VALUES (14, 'Clothes', 0, 1)
INSERT INTO @Categories VALUES (15, 'T-Shirts', 14, 2)
INSERT INTO @Categories VALUES (16, 'Red', 15, 3)
INSERT INTO @Categories VALUES (17, 'Denim', 14, 2)
INSERT INTO @Categories VALUES (18, 'Levise', 17, 3)

DECLARE @Product_Category_Mappings TABLE(MappingId INT NOT NULL,
                                         ProductId INT NOT NULL,
                                         CategoryId INT NOT NULL)

INSERT INTO @Product_Category_Mappings VALUES (100, 1, 10)
INSERT INTO @Product_Category_Mappings VALUES (101, 1, 11)
INSERT INTO @Product_Category_Mappings VALUES (102, 1, 12)

INSERT INTO @Product_Category_Mappings VALUES (103, 2, 10)
INSERT INTO @Product_Category_Mappings VALUES (104, 2, 11)
INSERT INTO @Product_Category_Mappings VALUES (105, 2, 12)

INSERT INTO @Product_Category_Mappings VALUES (106, 3, 14)
INSERT INTO @Product_Category_Mappings VALUES (107, 3, 15)
INSERT INTO @Product_Category_Mappings VALUES (108, 3, 16)

INSERT INTO @Product_Category_Mappings VALUES (109, 4, 14)
INSERT INTO @Product_Category_Mappings VALUES (110, 4, 17)
INSERT INTO @Product_Category_Mappings VALUES (111, 4, 18)

SELECT * 
FROM @Products  P
INNER JOIN @Product_Category_Mappings M ON M.ProductId = P.ProductId
INNER JOIN @Categories C ON C.CategoId = M.CategoryId
WHERE M.ProductId = P.ProductId 
ORDER BY P.ProductId, C.DisplayOrder

Result of the above script. How I get highlighted rows?

Answer 1

For each ProductId, you want the row with highest DisplayOrder. You can use window functions:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY P.ProductId ORDER BY C.DisplayOrder DESC) rn
    FROM @Products  P
    INNER JOIN @Product_Category_Mappings M ON M.ProductId = P.ProductId
    INNER JOIN @Categories C ON C.CategoId = M.CategoryId
    WHERE M.ProductId = P.ProductId 
) t
WHERE rn = 1
ORDER BY P.ProductId, C.DisplayOrder