Oct 28, 2024
0
Level 1
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.
Please or to participate in this conversation.