By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
--https://stackoverflow.com/q/79529525/5298879
--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?
create table "TableName"("FieldName")as values
('aac')
,('a|c')
,('bad')
,('b|d');
SELECT 4
--`select distinct` requires that it's corresponding `order by`
--uses the exact fields that are being selected
select distinct "FieldName"
from "TableName"
order by "FieldName" like '%|%' DESC
, "FieldName" ASC;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 5: order by "FieldName" like '%|%' DESC ^
--you need to nest that in a subquery/CTE
select*from(select distinct "FieldName"
from "TableName")_
order by "FieldName" like '%|%' DESC
, "FieldName" ASC;
| FieldName |
|---|
| a|c |
| b|d |
| aac |
| bad |
SELECT 4