Having this situation:
SELECT DISTINCT "FieldName"
FROM "TableName"
ORDER BY "FieldName" ASC;
I'd like to have the lines containing '%|%'
first followed by the ones without '%|%'
.
How to achieve this?
Use a boolean
expression on the first position of your order by
list. Since true
is 1
, it's larger than a 0
/false
so you need to make that descending
:
demo at db<>fiddle
select*from(select distinct "FieldName"
from "TableName") as subquery
order by "FieldName" like '%|%' desc
, "FieldName" asc;
FieldName |
---|
a|c |
b|d |
aac |
bad |
A select distinct
requires its order by
to use the exact fields that are being selected, so it needs to be nested in a subquery before ordering under a regular select outside.
To avoid it, you can switch to a distinct on
(as suggested by @Erwin Brandstetter), or a plain group by
- after all, it'll extract distinct groups:
select "FieldName"
from "TableName"
group by "FieldName"
order by strpos("FieldName",'|')>0 desc
, "FieldName";
If you're just looking for a |
character, a much more limited but simpler and faster strpos()
should outperform pattern matching with like '%|%'
, similar to '%\|%'
and ~ '\|'
.