I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that value i represents the ith occurrence of that key (but I want that ordering to be random). I thought perhaps some sort of auto increment would work, but I have multiple keys and thus would need multiple columns (I believe only one auto increment is permitted).

The solution I came up with doesn't seem to be working. First I created a new table to store the columns I am interested in, plus one extra column, rnd. I would use rnd later as a way to to ORDER BY rand() in a context where I need an actual column and not a function. Here's how I accomplished the first bit:

SET @rnd = 0;
INSERT INTO new_table SELECT col1, ..., colN, @rnd := @rnd+1 FROM original_table ORDER BY rand();

Next I would add an auto increment based upon the key, and I would make use of the rnd column I just created. The ALTER TABLE...ORDER BY does not permit ordering by a function (like rand()), so that's why I needed to create the rnd column first:

ALTER TABLE new_table ADD first_index INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD KEY(col1, col2, first_index), ORDER BY rnd;

I would then remove the auto increment status and repeat for each key:


At first this seemed to do the trick. However, upon closer inspection, it appears as though the random ordering was not respected. The index columns appeared to be created in the same order that the original table happened to use. I'm rather frustrated as I went through several very awkward steps to ensure a random ordering, and that did not even work. Why didn't my solution work, and is there a more elegant way to do this?

EDIT: It was apparently not clear what I was looking for, so I'll try a simple example:

Col1   Col2   <other columns>
1997   A
1997   B
2001   B
1997   A
2001   B
1997   A
2001   A
1997   B

The above ordering reflects the original ordering which is neither truly predictable nor truly random. After adding my rnd column and sorting by it I would have something like this:

Col1   Col2   <other columns>   rnd
1997   B                        1
2001   A                        2
1997   B                        3
2001   B                        4
1997   A                        5
1997   A                        6
1997   A                        7
2001   B                        8

I would then get the first index like this:

Col1   Col2   <other columns>   rnd    first_index
1997   B                        1      1
2001   A                        2      1
1997   B                        3      2
2001   B                        4      1
1997   A                        5      1
1997   A                        6      2
1997   A                        7      3
2001   B                        8      2

So 1997 with B appeared 2 times so those values are numbered from 1-2, whereas 1997 with A appeared 3 times so those values rows are numbered 1-3. I used the ORDER BY rnd so that it was random as to which row of 1997-A got assigned index 1, which one got 2, and which one got 3 (but it would be deterministic after that initial assignment of course).

EDIT: I'll add another example since my goal seems unclear. The MySQL docs show a method to get almost there:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)

INSERT INTO animals (grp,name) VALUES

SELECT * FROM animals ORDER BY grp,id;

This creates a table which, although not what I want, gets close:

| grp    | id | name    |
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |

This close, but I think there are at least two reasons why this doesn't work for me. First of all, I need to do it on an existing table, not on one that I'm creating. Secondly I don't want dog to show up first in the mammal group just because it was first in the underlying table. I want to, one time, randomize that order. From then on the ordering will be deterministic, but I want it to be random first.

If you are wondering about my motivation for wanting to do this, here is a question explaining what I'm trying to do with this. Also note that the solution should be in MySQL.

  • 3
    If the ith value represents row i then it isn't random order. Row i in the table is unrelated to any FKs. The same order every time is not random: its deterministic. Can you give sample data: your explanation isn't clear....
    – gbn
    Mar 28 '11 at 5:32
  • I need to one time create a random order and then after that it will be deterministic. I'll try to edit with an example. Mar 28 '11 at 5:37
  • See my answer to your other question which I think is your real problem...
    – gbn
    Mar 28 '11 at 5:40
  • It looks like you want each grouping of rows with the same Col1 and Col2 values to be randomly ordered within gthe group and then numbered. If so, then in Oracle I would do this: SELECT Col1, Col2, ..., Row_Number() OVER (PARTITION BY Col1, Col2 ORDER BY rnd) FROM new_table; I can't translate to MySQL. Apr 1 '11 at 13:11

Here's what I would do. I hope I did understand your question correctly. It's a workaround and perhaps not very elegant:

  • create a random field, and then an AUTO_INCREMENT field ("ID") as the single field of a primary key, order by RND etc
  • create a temporary table with MIN(ID) for each combination of Col1/2 (first, but randomly):

  MIN(ID) AS MIN_ID, Col1, Col2 
 Col1, Col2
  • add a second INT field to be the first_index
  • update the table and set first_index to be the difference between the minimum value and the running value of the autoincremented field:

  table t, temp_table tmp
  t.first_index = (t.ID - tmp.MIN_ID) + 1
  t.Col1=tmp.Col1 AND t.Col2=tmp.Col2 
  • optionally remove the random and ID field, and add a new PK with the three fields Col1/Col2/first_index (auto incremented) for following inserts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.