There is a table, even for a "synthetic" example - just a table of integers. Numbers can be repeated and cannot be NULL. It is necessary to display a sorted list in ascending "distance".

Definition of "distance"

Dij = ABS(ABS(Ti)-ABS(Tj))

Where:

  • Dij - the distance between the i-th and j-th element
  • Ti,Tj - the i-th and j-th elements of the table

Sorting rules

  • Dij<Djk
  • If several pairs apply for the next iteration of sorting to the place of the next pair ( Dij==Djk ), the pair that has a number is selected - the smallest of all the numbers from these pairs
  • possibly Ti < Tj

Amendment

"Increasing the distance" - most likely not quite the correct wording. Because the choice of the next pair depends on the previous one. In other words, at the next iteration, the second element of the previous pair becomes the first in the current one. Therefore, the "distance" can "dance." Type 2-3-10-1-2-2-7-3 ...

For the test

 CREATE TABLE Test ( Digit INT NOT NULL ); INSERT INTO Test (Digit) VALUES (17),(16),(9),(8),(7),(3),(0),(-5),(-10),(-17);

The desired sorting order:

 -17 17 16 -10 9 8 7 -5 3 0
  • Comments are not intended for extended discussion; conversation moved to chat . - PashaPash ♦

1 answer 1

SQLite, postgesql:

 WITH RECURSIVE Q(Digit,ids,Level) as( select * from ( select a.Digit,','||a.rowid ids,0 as Level from Test a, Test b where a.rowid!=b.rowid order by abs(abs(a.Digit)-abs(b.Digit)),a.Digit limit 1 ) A union all select a.Digit,ids||','||a.rowid,Level+1 from Q, Test a where a.rowid in( select b.rowid from Test b,(select Q.digit as d) C where Q.ids||',' not like '%,'||b.rowid||',%' order by abs(abs(b.Digit)-abs(Cd)),b.Digit limit 1 ) ) select Digit from Q order by Level

The "strange" subquery (select Q.digit as d) had to be entered specifically for SQLite, because it does not want to see the fields of the table Q in the phrase by order by subquery directly. For posgresql, this is not required and Q.Digit can be specified directly in order by .

On MySQL, I have no desire to solve this, since there are no recursive queries. Although, in principle, recursion can be emulated by replicating records and accumulating “left” numbers in variables, much like in this answer .

  • Mike, just thank you so much !!! I will understand and learn)) - Majestio