Relevant SQL Fiddle (postgres 9.2)
How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column?
That is, for rows of the column "rank" (1,3,3,1) how do I get a result so that rows of that column are ordered as (1,3,1,3), garuanteeing that a row with column value 1 is never follow by a row with column value 1, and similar for other rows values.
Best Answer
You cannot avoid in it in all cases (if more than 50% of the rows have the same value in the column.) One way to achieve a "shuffle" result, similar to what you want is by using window function:
The above will not avoid all cases though. If for example, the values in the column are
1,1,1,1,2,2,3
, you'll get:and not the (better):