What's the best way to combine row_number() and case statements for this SQL challenge?

Data and Databases
1

I’m working on an SQL problem and I’m stuck. The task is about renumbering unique pairs of maker and type from a Product table. Here’s what I need to do:

  1. Order by manufacturer name (ascending)
  2. Order product types as PC, Laptop, Printer
  3. Show manufacturer name only in the first row for each maker

My current query looks like this:

SELECT 
    ROW_NUMBER() OVER (ORDER BY 
        manufacturer ASC, 
        CASE product_type 
            WHEN 'Desktop' THEN 1 
            WHEN 'Notebook' THEN 2 
            WHEN 'Printing' THEN 3 
        END
    ) AS row_num, 
    manufacturer, product_type 
FROM inventory 
ORDER BY manufacturer, 
    CASE product_type 
        WHEN 'Desktop' THEN 1 
        WHEN 'Notebook' THEN 2 
        WHEN 'Printing' THEN 3 
    END

But my output isn’t correct. It’s not grouping manufacturers or showing empty strings for repeated makers. Any ideas on how to fix this? I’m using SQL Server 2012 for this exercise.

2

hey ryan, looks like ur almost there! try adding a PARTITION BY clause to ur ROW_NUMBER() function. something like:

ROW_NUMBER() OVER (PARTITION BY manufacturer ORDER BY …)

this should group by manufacturer. for empty strings, u can use a CASE statement in the SELECT. hope this helps!

3

Your approach is sound, but you need a few adjustments to achieve the desired result. Consider using DENSE_RANK() instead of ROW_NUMBER() to handle the unique pairs. For the manufacturer name, you can employ a window function with FIRST_VALUE(). Here’s a modified version of your query:

SELECT
DENSE_RANK() OVER (ORDER BY manufacturer, CASE product_type WHEN ‘Desktop’ THEN 1 WHEN ‘Notebook’ THEN 2 WHEN ‘Printing’ THEN 3 END) AS row_num,
CASE WHEN ROW_NUMBER() OVER (PARTITION BY manufacturer ORDER BY product_type) = 1 THEN manufacturer ELSE ‘’ END AS manufacturer,
product_type
FROM inventory
ORDER BY manufacturer, CASE product_type WHEN ‘Desktop’ THEN 1 WHEN ‘Notebook’ THEN 2 WHEN ‘Printing’ THEN 3 END

This should give you the correct grouping and empty strings for repeated manufacturers. Let me know if you need further clarification.