Sort to have non-consecutive column values

Question

is this possible in mysql queries? Current data table is:

id - fruit - name
1 - Apple - George
2 - Banana - George
3 - Orange - Jake
4 - Berries - Angela

In the name column, i would like to sort it so there is no consecutive name on my select query.

My desires output would be, no consecutive george in name column.

id - fruit - name
1 - Apple - George
3 - Orange - Jake
2 - Banana - George
4 - Berries - Angela

Thanks in advance.

That's difficult. And it's rather the opposite of one would call sorting :-) What if you have Angela-George-George-George? There is no way to sort these four so to have all George separate. What is the query to do in such situation? Crash? — Thorsten Kettner, Oct 23 '18 at 12:52
You have tagged your request "sql". But you are not looking for an answer on SQL (e.g. how to fix your SQL query or how to apply your algorithm in SQL - you have neither a query nor even an algorithm). You are looking for an algorithm instead. So you should remove the database-related tags and replace them with an "algorithm" tag. — Thorsten Kettner, Oct 23 '18 at 13:45
I've added the "algorithm" tag. I've also finally come up with an answer showing a suitable algorithm and even a standard SQL query applying that algorithm. — Thorsten Kettner, Oct 24 '18 at 23:03

score 2 · Answer 1 · answered Oct 23 '18 at 12:20

2

In MySQL 8+, you can do:

order by row_number() over (partition by name order by id)

In earlier versions, you can do this using variables.

answered Oct 23 '18 at 12:20

Gordon Linoff

1,242,037
58
646
786

Madhur Bhaiya · Answer 2 · 2018-10-23T13:04:36.353

1

Following solution would work for all the MySQL versions, especially version < 8.0

In a Derived table, first sort your actual table, using name and id.
Then, determine the row number for a particular row, within all the rows having same name value.
Now, use this result-set and sort it by the row number values. So, all the rows having row number = 1 will come first (for all the different name value(s)) and so on. Hence, consecutive name rows wont appear.

You can try the following using User-defined Session Variables:

SELECT dt2.id,
       dt2.fruit,
       dt2.name
FROM   (SELECT @row_no := IF(@name_var = dt1.name, @row_no + 1, 1) AS row_num,
               dt1.id,
               dt1.fruit,
               @name_var := dt1.name                               AS name
        FROM   (SELECT id,
                       fruit,
                       name
                FROM   your_table_name
                ORDER  BY name,
                          id) AS dt1
               CROSS JOIN (SELECT @row_no := 0,
                                  @name_var := '') AS user_init_vars) AS dt2
ORDER  BY dt2.row_num,
          dt2.id

DB Fiddle DEMO

edited Oct 23 '18 at 13:04

answered Oct 23 '18 at 12:25

Madhur Bhaiya

28,155
10
49
57

Hi, im testing this approach but when I test to add another george row, it still display as consecutive https://www.db-fiddle.com/f/8amYX6iRu8AsnYXJYz15DF/0 – bumbumpaw Oct 23 '18 at 13:09
1

@bumbumpaw It is what Thorsten [suggested](https://stackoverflow.com/questions/52948761/sort-to-have-non-consecutive-column-values/52949055?noredirect=1#comment92806976_52948761) . Sorting will not work if you have more rows of same name than others. Very difficult to achieve in that case. – Madhur Bhaiya Oct 23 '18 at 13:10
1

Hmm, ok ok I get it. I need to manipulate it with using php then so to save result it to another array. something like that. – bumbumpaw Oct 23 '18 at 13:18
@bumbumpaw Even PHP wont be able to help. Let's say you have total 10 rows and 6 rows for George in that. How would you fit in all George name(s), so that they are not consecutive – Madhur Bhaiya Oct 23 '18 at 13:25
1

@bumbumpaw: Your altered fiddle shows why this is so difficult. The only solution to sort three Georges, one Jake and one Angela would be to make Jake and Angela rows #2 and #4. However, from a mere database point of view, Jake is like Angela; they both have one single row in the table, so what rule to apply to make one row #2 and one row #4? What you really need is an algorithm for the task. And then it may be easier to apply that algorithm with a programming language, rather than SQL. – Thorsten Kettner Oct 23 '18 at 13:56

score 1 · Answer 3 · answered Oct 23 '18 at 12:44

Another idea...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,'George'),
(2,'George'),
(3,'Jake'),
(4,'Angela');

SELECT x.* 
  FROM my_table x
  JOIN my_table y 
    ON y.name = x.name 
   AND y.id <= x.id
 GROUP 
    BY x.id
 ORDER
    BY COUNT(*)
     , id;

+----+--------+
| id | name   |
+----+--------+
|  1 | George |
|  3 | Jake   |
|  4 | Angela |
|  2 | George |
+----+--------+

Thorsten Kettner · Answer 4 · 2018-10-24T19:52:33.970

Here is my algorithm:

count each name's frequency
order by frequency descending and name
cut into partitions as large as the maximum frequency
number rows within each partition
order by row number and partition number

An example: Names A, B, C, D, E

 step 1 and 2
 ------------
 AAAAABBCCDDEE

 step 3 and 4
 ------------
 12345     
 AAAAA
 BBCCD
 DEE

 step 5
 ------
 ABDABEACEACAD

The query:

with counted as
(
  select id, fruit, name, count(*) over (partition by name) as cnt
  from mytable 
)
select id, fruit, name
from counted
order by
  (row_number() over (order by cnt desc, name) - 1) % max(cnt) over (),
  row_number() over (order by cnt desc, name);

Common table expression (WITH clauses) and window functions (aggregation OVER) are available as of MySQL 8 or MariaDB 10.2. Before that you can retreat to subqueries, which will make the same query quite long and hard to read, though. I suppose you could also use variables instead, somehow.

DB fiddle demo: https://www.db-fiddle.com/f/8amYX6iRu8AsnYXJYz15DF/1

Sort to have non-consecutive column values

4 Answers4

DB Fiddle DEMO

Linked