Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE comments (id int, COMMENT text);

INSERT INTO comments VALUES
  (1, 'other comment')
, (2, 'my comment')
, (3, 'my comment')
, (4, 'other comment')
, (5, 'my comment')
, (6, 'other comment')
, (7, 'some comment')
, (8, 'a comment')
;
;

CREATE TABLE



INSERT 0 8


-- or 'my comment', then sort sort by id:
SELECT *
FROM   comments
ORDER  BY comment <> 'my comment', id;


id
comment


2
my comment

3
my comment

5
my comment

1
other comment

4
other comment

6
other comment

7
some comment

8
a comment



SELECT 8


-- or 'my comment' first, then sort other comments, then sort by id:
SELECT *
FROM   comments
ORDER  BY comment <> 'my comment', comment, id;


id
comment


2
my comment

3
my comment

5
my comment

8
a comment

1
other comment

4
other comment

6
other comment

7
some comment



SELECT 8


-- if there can be null values:
INSERT INTO comments VALUES
  (null, 'my comment')
, (9, null)
, (null, 'other comment')
, (null, 'some comment')
;

INSERT 0 4


-- those sort last in default sort order:
SELECT *
FROM   comments
ORDER  BY comment <> 'my comment', comment, id;


id
comment


2
my comment

3
my comment

5
my comment

null
my comment

8
a comment

1
other comment

4
other comment

6
other comment

null
other comment

7
some comment

null
some comment

9
null



SELECT 12

id	comment
2	my comment
3	my comment
5	my comment
1	other comment
4	other comment
6	other comment
7	some comment
8	a comment