How to prioritize rows with specific condition when on Postgres?

Question

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:

There's a lot of types (could be in thousands of different types)
I don't care the order of the result other than I want to prioritize getting fruit type first

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

@wildplasser Thank you, that solves it!

yonder
– yonder

2021-09-13 10:33:21 +00:00
Commented Sep 13, 2021 at 10:33 — yonder
– yonder, Commented Sep 13, 2021 at 10:33

wildplasser · Accepted Answer · 2021-09-13 10:33:01Z

4

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

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

answered Sep 13, 2021 at 10:33

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to prioritize rows with specific condition when on Postgres?

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