Sort SQL Oracle query according to specific values order

Question

My task is to display first the number of products in category 5, then category 1 and then 2. This is the query I have:

SELECT 
      pc.category_name, p.category_id, COUNT(p.category_id) AS num_of_products 
FROM 
     products p 
INNER JOIN 
     product_categories pc 
ON 
     p.category_id = pc.category_id
WHERE 
     p.category_id != 4 AND pc.category_id != 4
GROUP BY  
     pc.category_name, p.category_id;

And this is the output:

**CATEGORY_NAME CATEGORY_ID NUM_OF_PRODUCTS**
  CPU           1           70
  Video Card    2           50
  Storage       5           108

How can I sort it in the required way? Maybe displaying the num_of_products column in a descendent order?

Thanks in advance.

Try with ORDER BY NUM_OF_PRODUCTS DESC

lsanchezo
– lsanchezo

2022-06-14 22:57:00 +00:00
Commented Jun 14, 2022 at 22:57 — lsanchezo
– lsanchezo, Commented Jun 14, 2022 at 22:57

MT0 · Accepted Answer · 2022-06-14 22:57:29Z

My task is to display first the number of products in category 5, then category 1 and then 2.

Use a CASE expression in the ORDER BY clause to translate different categories into the order priorities:

SELECT pc.category_name,
       p.category_id,
       COUNT(p.category_id) AS num_of_products 
FROM   products p 
       INNER JOIN product_categories pc 
       ON p.category_id = pc.category_id
WHERE  p.category_id != 4
GROUP BY  
       pc.category_name,
       p.category_id
ORDER BY
       CASE p.category_id
       WHEN 5 THEN 1
       WHEN 1 THEN 2
       WHEN 2 THEN 3
       ELSE 4
       END;

If you really mean that you want to order by the num_of_products in descending order (rather than ordering by the categories that happen to correspond with that) then just use the num_of_products in the ORDER BY clause:

SELECT pc.category_name,
       p.category_id,
       COUNT(p.category_id) AS num_of_products 
FROM   products p 
       INNER JOIN product_categories pc 
       ON p.category_id = pc.category_id
WHERE  p.category_id != 4
GROUP BY  
       pc.category_name,
       p.category_id
ORDER BY
       num_of_products DESC;

Collectives™ on Stack Overflow

Sort SQL Oracle query according to specific values order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related