CODEWITHSUNDEEP
sqlpostgresql
jedi
jedi

Reputation: 2198

Order rows by product category name

My customer want me to order products differently per different category, for example when the category name is 'alternator' I should first display parts whose producer is 'ABC', when the category name is 'starter', I should first display parts whose producer is 'DEF', otherwise order by images_count and parameters_count. My schema looks like this.

Parts(id, producer_id, category_id) Category(id, name) Producer(id, name)

I've tried by ordering the results in ORDER BY, like so.

SELECT *
FROM   parts 
       JOIN categories 
         ON parts.category_id = categories.id 
       JOIN producers 
         ON parts.producer_id = Cast(producers.lauber_id AS INT) 
ORDER  BY CASE 
            WHEN categories.NAME = 'alternator' 
                  OR categories.NAME = 'starter' THEN 
            producers.NAME = 'STARDAX' 
             OR producers.NAME = 'POWERTRUCK' 
          END DESC, 
          CASE 
            WHEN categories.NAME = 'something else' THEN 
            producers.NAME = 'POINTGEAR' 
             OR producers.NAME = 'LAUBER' 
          END DESC, 
          images_count DESC, 
          parameters_count DESC

I am not sure if I am doing this right, I am not a master of SQL. I am using Postgres.

Upvotes: 1

Views: 465

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You could use CASE:

SELECT *
FROM   parts 
       JOIN categories 
         ON parts.category_id = categories.id 
       JOIN producers 
         ON parts.producer_id = Cast(producers.lauber_id AS INT) 
ORDER  BY CASE WHEN categories.NAME = 'alternator' AND producers.NAME = 'ABC' THEN 0
               WHEN categories.NAME = 'starter' AND producers.NAME = 'DEF' THEN 0
               -- ...
               ELSE 1
          END,
          images_count DESC, 
          parameters_count DESC

Upvotes: 1

Related Questions