sqlpostgresqlsql-order-by

SELECT ... ORDER BY <boolean condition>


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?


Solution

  • 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 ~ '\|'.