Postgresql - Conditional Ordering

PostgreSQL - conditional ordering

conditional order can be performed with CASE, like here:

select *
    from test
order by 
    flag
  , case when flag then date end desc
  , case when not flag then date end asc

Postgres order by condition when and then

Based on your description, that should be

ORDER BY CASE WHEN created <= '2020-09-14 10:00:00
              THEN "type"
         END,
         created DESC

Postgresql conditional order by with multiple cases

Although dynamic SQL should be used rarely, this is case where the performance probably will be better:

BEGIN
  ..
  EXECUTE format('SELECT .. ORDER BY %I %s', _sortcol, 
    CASE _sortdir WHEN 'desc' THEN 'desc'
                  WHEN 'asc' THEN 'asc'
                  ELSE 'asc' END);

When you use dynamic SQL, then you have to think about protection against SQL injection every time. Every string parameter have to be escaped or limited to safe value only.

Postgres: conditional ORDER BY

You can do what you describe using window functions. Something like this:

select *
from example e
order by primary_sort,
         (case when count(*) over (partition by primary_sort, record_id) > 1 then record_sort
          end) nulls last,
         alt_sort;

This does not return the results you specify. But some variation is probably what you intend.

ORDER BY CASE WHEN ... ELSE ... END in PostgreSQL

You can use this trick to shorten the logic:

ORDER BY (CASE WHEN boolean_column THEN text_column END) ASC,
         text_column DESC

It is still two order by keys though.

Conditional Order By

SQL Fiddle

select 
    row_number() over() id, *
from (
    select
        r.routeid,
        p.pointid,
        label,
        type,
        labelstart,
        labelend
    from
        route r
        inner join
        point p on p.routeid = r.routeid
    where
        r.type = 'E' and p.label % 2 = 0
        or
        r.type = 'O' and p.label % 2 != 0
        or
        r.type = 'A'
    order by
        r.routeid, r.progres, r.id,
        case labelstart < labelend
            when true then label
            else label * - 1
        end
) s

---

Related Topics