CODEWITHSUNDEEP
sqlpostgresql
Joakim
Joakim

Reputation: 2152

Postgres: conditional ORDER BY

Consider the table below: (here's a db-fiddle with this example)

id   primary_sort   record_id   record_sort  alt_sort
1    2              1           11           100
2    2              2           10           101
3    3              1           12           108
4    3              1           13           107
5    3              2           14           105
6    1              2           15           109

I'd like to sort this according to primary_sort first. If equal, the next sort field depends on the value of record_id: if two rows has the same record_id, then sort them by record_sort. Otherwise, sort them by alt_sort. I think the query should look something like this:

select * from example
order by 
  primary_sort,
  case
    when [this_row].record_id = [other_row].record_id
    then record_sort
    else alt_sort
  end
;

Expected output:

id   primary_sort   record_id   record_sort  alt_sort
6    1              2           15           109
1    2              1           11           100
2    2              2           10           101
5    3              2           14           105
3    3              1           12           108
4    3              1           13           107

Here's some pseudocode in Java, showing my intent:

int compareTo(Example other) {
    if (this.primary_sort != other.primary_sort)
    {
        return this.primary_sort.compareTo(other.primary_sort);
    }
    else if (this.record_id == other.record_id)
    {
        return this.record_sort.compareTo(other.record_sort);
    }
    else
    {
        return this.alt_sort.compareTo(other.alt_sort);
    }
}

(this is a minimal, reproducible example. Similar SO questions I've found on conditional order by are not applicable, because my condition is based on values in both rows (i.e. [this_row].record_id = [other_row].record_id))

Upvotes: 0

Views: 215

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use the analytical function as follows:

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

Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271171

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.

Upvotes: 1

Related Questions