CODEWITHSUNDEEP
sqlpostgresqloptimization
yonder
yonder

Reputation: 45

How to prioritize rows with specific condition when on Postgres?

I have this table as an example:

CREATE TABLE "public"."items" (
    "name" text NOT NULL,
    "type" text NOT NULL
)

With initial values:

INSERT INTO "items" ("name", "type") VALUES
('apple',   'fruit'),
('banana',  'fruit'),
('chair',   'furniture'),
('table',   'furniture'),
('grape',   'fruit'),
('cabbage', 'vegetable'),
('beef',    'meat'),
('water',   'drinks'),
('lamp',    'furniture');

How can I query rows from this table with one statement so that I get the rows with the fruit type first?

Given that:

For example if I want to query 5 rows from this table, the result would be 'apple', 'banana', 'grape', 'cabbage' and 'beef'.

Upvotes: 2

Views: 460

Answers (1)

wildplasser
wildplasser

Reputation: 44240

You can use a boolean expression as the first ORDER BY expression:

SELECT * FROM items
ORDER BY (type='fruit') DESC, name
   ;

Upvotes: 4

Related Questions