Is there any way to mix(change order) rows in mysql table?

Question

I had 3 tables which are not identical to each other. According to one of my requirement I had to copy all these tables records to another table. That part is okay.What my problem is that the records I inserted is in a order now. Like

first 100 records from table1
second 100 records from table2
third 100 records from table3

what I wanted to do is change/mix the record positions.Like if i selected first 100 records there should be records from all three table.

selecting data from ORDER BY Rand() is not I want.I just need to select data and display those data. Is there any way that i can solve this out?Thanks

If you want data in a particular order, there is **one** way to guarantee that - and that's by providing an `ORDER BY` clause on the outermost `SELECT` query, when retrieving the rows. — Damien_The_Unbeliever, Jan 16 '13 at 07:38
To randomise order of a select statement, you can use the [rand](http://dev.mysql.com/doc/refman/5.5/en/mathematical-functions.html#function_rand) function. — hd1, Jan 16 '13 at 07:40
When you are inserting, do you have a unique index number for each record? — bonCodigo, Jan 16 '13 at 07:42
Here is a post to check more solutions: http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql — bonCodigo, Jan 16 '13 at 07:48
@bonCodigo In my new table, I have a primary key with auto increment.I just copy data to new table without previous primary ids of other tables — vinu, Jan 16 '13 at 07:50
@vinu check my comment to Meherzad. His query will work as long as you really don't have a condition to choose x number of rows e.g. Limit 50 rows out of 300..randomly.. — bonCodigo, Jan 16 '13 at 08:07

score 0 · Answer 1 · answered Jan 16 '13 at 07:42

0

A great post handling several cases, from simple, to gaps, to non-uniform with gaps.

http://jan.kneschke.de/projects/mysql/order-by-rand/

For most general case, here's how you do it:

SELECT name
FROM random AS r1 JOIN
   (SELECT (RAND() *
                 (SELECT MAX(id)
                    FROM random)) AS id)
    AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1

This supposes that the distribution of ids is equal, and that there can be gaps in the id list. See the article for more advanced examples

answered Jan 16 '13 at 07:42

Meherzad

7,972
1
26
39

If you take a look at [this](http://sqlfiddle.com/#!2/e8509/6) doesn't really seem to gurantee *fully random* but random-consecutive ;) In this case it doesn't gurantee to [give x number](http://sqlfiddle.com/#!2/e8509/6) of rows either.. – bonCodigo Jan 16 '13 at 08:03
@bonCodigo Yes friend i already tried this but it won't work as i expected – vinu Jan 16 '13 at 08:37

score 0 · Answer 2 · edited May 23 '17 at 12:12

0

If you don't want to query later on with rand() you could create the table by inserting from a union select ordered by rand() in the first place:

INSERT INTO merged (a, b)
SELECT a, b FROM (
  SELECT a, b, rand() AS r FROM t1
  UNION ALL
  SELECT a, b, rand() AS r FROM t2
) ORDER BY r

However, also consider this post I just came across: INSERT INTO SELECT strange order using UNION, perhaps someone can comment.

edited May 23 '17 at 12:12

Community

1
1

answered Jan 16 '13 at 07:42

s.bandara

5,553
1
19
36

Thanks friend i got the point.Since my table are so much different from each other creating a table again with this is little bit struggle for me.I will try.. – vinu Jan 16 '13 at 08:07

Is there any way to mix(change order) rows in mysql table?

2 Answers2