0

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.

  • Not as a built in, you would have to generate a column which has a number which represents the desired order and then sort by it. "Biggest distance of same values" sounds however like a non trivial thing to calculate. – eckes Apr 29 '17 at 23:57
  • May be an interesting problem if you define it more closely. It will be hard to implement with SQL because multiple sets of same values compete for the spots at top and bottom of the list for "maximum distance", making it a non-linear optimization problem. I suggest you add a test setup with dummy rows and the desired output to clarify. – Erwin Brandstetter Apr 30 '17 at 3:42
  • Arranging data in any manner is order.May be in your case first serial no generated as per your requirement then finally order by that new serial no. – KumarHarsh May 1 '17 at 10:59
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.