sql - SELECT ... ORDER BY <boolean condition> - Stack Overflow

admin管理员组

文章数量:1406924

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?

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?

• sql
• postgresql
• sql-order-by

Share Improve this question Follow edited Mar 24 at 7:43 Erwin Brandstetter 661k157157 gold badges1.1k1.1k silver badges1.3k1.3k bronze badges asked Mar 23 at 18:50 StOMichaStOMicha 42311 gold badge77 silver badges1616 bronze badges 1

• @Charlieface The threads you linked concern conditional ordering but don't address the distinct case. Please don't cross-link RDBMS' - PostgreSQL supports direct boolean expressions in order by, SQL Server doesn't, requiring a case wrapper around them. – Zegarek Commented Mar 24 at 15:30

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

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

With DISTINCT ON no subquery is required:

SELECT DISTINCT ON (fld, fld ~ '\|')
       fld
FROM   tbl
ORDER  BY fld ~ '\|' DESC, fld;

fiddle

fld ~ '\|' is effectively the same as fld LIKE '%|%'.

About DISTINCT ON:

• Select first row in each GROUP BY group?

About sorting by a boolean expression:

• Sorting null values after all others, except special

You may want to sort nulls last:

• Sort NULL values to the end of a table

本文标签： sqlSELECTORDER BY ltboolean conditiongtStack Overflow