TransWikia.com

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 $$ 
begin
  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!

Ask a Question

Get help from others!

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