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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP