Syed1980's avatar

How to Sort SQL Results by Category and Subcategory for Each Owner?

This is the raw sql query,

 SELECT * 
 FROM transactions t
 WHERE is_submitted = 0
 ORDER BY 
 owner_id,  -- Grouping by owner_id
 CASE 
    -- MC Craft
    WHEN (category_id = 1 AND subcategory_id = 1) THEN 1  -- MC Craft New Registration
    WHEN (category_id = 1 AND subcategory_id = 2) THEN 2  -- MC Craft Inspection
    WHEN (category_id = 1 AND subcategory_id = 3) THEN 3  -- MC Craft Renewal
    WHEN (category_id = 1 AND subcategory_id = 4) THEN 4  -- MC Craft Amendment
    
    -- Coast Guard
    WHEN (category_id = 2 AND subcategory_id = 7) THEN 5  -- Coast Guard New Registration
    WHEN (category_id = 2 AND subcategory_id = 8) THEN 6  -- Coast Guard Renewal
    WHEN (category_id = 2 AND subcategory_id = 9) THEN 7  -- Coast Guard Amendment
    
    -- MC License
    WHEN (category_id = 1 AND subcategory_id = 17) THEN 8 -- MC License New
    WHEN (category_id = 1 AND subcategory_id = 18) THEN 9 -- MC License Renewal
    WHEN (category_id = 1 AND subcategory_id = 19) THEN 10 -- MC License Amendment

    ELSE 11
END,
receipt_date;  

with this query I'm trying to sort the data based on the category, subcategory & owner.

I want the results to be like this

for all the owners category_id = 1 AND subcategory_id = 1, 2, 3, 4.

for all the owners category_id = 2 AND subcategory_id = 7, 8, 9.

for all the owners category_id = 1 AND subcategory_id = 17, 18, 19.

0 likes
0 replies

Please or to participate in this conversation.