Sort rows with given value before all other rows

Question

I found a related answer for SQL Server here.

In my case, I have a table column comment. How do I move 'my comment' to the top, and then sort by id?

other comment          my comment
other comment          my comment
other comment          my comment
my comment       =>    other comment
other comment          other comment
my comment             other comment
my comment             other comment

Answer 1

Assuming your objective is:

"Sort 'my comment' before other comments, then sort by id"

SELECT *
FROM   comments
ORDER  BY comment <> 'my comment', id;

Works because boolean false (think 0) sorts before true (think 1).
Or your objective is:

"Sort 'my comment' before other comments, then sort comments alphabetically, then sort by id"

SELECT *
FROM   comments
ORDER  BY comment <> 'my comment', comment, id;

fiddle

See (with links to more):

• PostgreSQL: order by column, with specific NON-NULL value LAST

Note that this requires proper handling of the boolean type, like Postgres implements it. SQL Server (your link) does not handle boolean values properly, so this wouldn't work over there.