Sort to have non-consecutive column values


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.


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.


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 | +----+--------+


Following solution would work for <a href="https://stackoverflow.com/questions/52948761/sort-to-have-non-consecutive-column-values/52949055#comment92807393_52948761" rel="nofollow">all the MySQL versions</a>, especially <strong>version < 8.0</strong>

<ul><li>In a <a href="https://dev.mysql.com/doc/refman/8.0/en/derived-tables.html" rel="nofollow">Derived table</a>, first sort your actual table, using name and id.</li> <li>Then, determine the row number for a particular row, within all the rows having same name value.</li> <li>Now, use this result-set and sort it by the <strong>row number</strong> values. So, all the rows having <em>row number = 1</em> will come first (for all the different name value(s)) and so on. Hence, consecutive name rows wont appear.</li> </ul>

You can try the following using <a href="https://dev.mysql.com/doc/refman/8.0/en/user-variables.html" rel="nofollow">User-defined Session Variables</a>:

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 <h2><a href="https://www.db-fiddle.com/f/b4DMTCR4KAiepoidHDptdL/0" rel="nofollow">DB Fiddle DEMO</a></h2>


Here is my algorithm:

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

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: <a href="https://www.db-fiddle.com/f/8amYX6iRu8AsnYXJYz15DF/1" rel="nofollow">https://www.db-fiddle.com/f/8amYX6iRu8AsnYXJYz15DF/1</a>


  • How to collect the return value of a function (Swift 3)
  • Mouse click simulates keyboard key in JavaScript
  • Windows 10 - System.Speech.Synthesis cannot get Mobile version of voice
  • Compiling GLUI library, VS2010 gives me a weird namespace error
  • Add comma between all names in a list of object
  • Echo specific rows from a mysql database
  • How to sort an array considering localization?
  • MS SQL Server 2008 :Getting start date and end date of the week to next 8 weeks
  • How to find data from last week in MySQL
  • Client side validation mvc dropdown
  • Pre-populated SQLite Database not reading properly in Android Studio
  • AlertDialog style when using setView()
  • Deploying a CodeRush plugin from the Community Site
  • Identifier too long in Oracle
  • Creating a Multidimensional, Associative Array in VBScript
  • How to override value that appears in a dropdown in the rails_admin gem
  • Eliminate partial duplicate rows from result set
  • Why querying a date BC is changed to AD in Java?
  • Database structure design with variable amounts of fields
  • ilmerge with a PFX file
  • Why value captured by reference in lambda is broken? [duplicate]
  • PHP - How to update data to MySQL when click a radio button
  • Fill an image in a square container while keeping aspect ratio
  • Join two tables and save into third-sql
  • How to handle AllServersUnavailable Exception
  • Convert array of 8 bytes to signed long in C++
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Trying to get generic when generic is not available
  • Understanding cpu registers
  • Why joiner is not used after Sequence generator or Update statergy
  • LevelDB C iterator
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • How can i traverse a binary tree from right to left in java?
  • UserPrincipal.Current returns apppool on IIS