The opposite of ordering by a column in SQL

Database Administrators Asked by John Hamelink on January 2, 2022

If you have a table and you’d like to sort by a column you can use order by foo to order the results by foo.

Is it possible to do the opposite, such that rows which have the same value for foo would be as far away from one another as possible?

I’m using Postgres 9.5 and would appreciate an answer that would be compatible with Postgres.

2 Answers

You can create an ordering number rn as:

select x, row_number() over (partition by x) as rn
from t
order by 2

Nothing guarantees that they will be as far as possible apart, but it will at least distribute each x over the result set

Answered by Lennart on January 2, 2022

Assuming that we have an equal amount of each value that you want to sort (in "maximum distance" style), you could do the following: [1] Create a "mapping table", that holds all distinct values that need sorting. [2] Select the values from the mapping table in the order (ascending/descending) as often needed for representing the stored values.

Proof of concept code:

create table t_ (
  id_ serial ,
  num_ integer

-- Stick some grouped values into the table.
do $$ 
  for outer_ in 128 .. 256 loop
    for inner_ in 1 .. 10 loop
      insert into t_ (num_) values (outer_);
    end loop;
  end loop;
end$$;  -- 1290 rows

When you run a select,

select * from t_ order by id_;

you will see that the values in num_ are grouped together. Then, create a "mapping" table, populate it with DISTINCT values - in (ascending) order - from the original table.

create table map_ (
  id_ serial ,
  num_ integer

insert into map_ (num_) 
select distinct num_ from t_ order by num_;
-- 129 rows

select * from map_;

Then, JOIN them, and use MOD() for getting the correct values in the output. The last select has a column for values in the original order, and another column for the new ("maximum distance") order.

select t_.id_ t_id_, map_.num_ "new ordering", t_.num_ "old ordering"
from map_ join t_ 
  on ( map_.id_ = mod(t_.id_ , ( select count(*) from map_ )))
order by t_.id_;

I know that this does not solve the problem if there are unequal amounts of "original" values. However, I think it is maybe a step in the right direction.

Answered by stefan on January 2, 2022

Add your own answers!

Related Questions

SHOW INNODB STATUS averages calculated

1  Asked on November 28, 2021 by antonios


IDE Access to Hive without user password?

1  Asked on November 28, 2021 by mayak


Daily Value of Stock Portfolio Postgres

1  Asked on November 28, 2021 by danields


How to remove dbcreator server role?

2  Asked on November 28, 2021 by andrea-antonangeli


why binlog doesn’t log the DML statement?

1  Asked on November 25, 2021 by lawrenceli


How to promote hidden mongo node to primary?

2  Asked on November 25, 2021 by sweeti-bharti


SQL Get Users By One Group But Exclude Others

1  Asked on November 25, 2021 by dangerman1988


Can’t find missing packages Oracle Linux 8

2  Asked on November 23, 2021 by cute-cake


Cassandra nodes are not equal

1  Asked on November 21, 2021 by ekrem-gurdal


MySQL sql_mode for remote access

1  Asked on November 21, 2021 by lakshmikantha


Error: MySQL shutdown unexpectedly

2  Asked on November 21, 2021 by md-maruf


Ask a Question

Get help from others!

© 2023 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP