I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want to group all of them together. It's not a simple
GROUP BY on the key but rather I want to be able to make groups of say 10. So if a particular key showed up 50 times I would get 5 results when I do this grouping (5 groups of 10). I also want this grouping to occur randomly within the key.
I didn't know of the direct way to do this, and the roundabout method I came up with isn't working like I think it should. The roundabout solution I came up with was 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 in random order). I could then do integer division so that every n (say 10) rows within the key have the same value, and I could do a
GROUP BY on that value.
Is there a more direct way to accomplish what I just described? It's quite awkward, and I ran into problems in creating the new index column (as I described in this question).
EDIT: First of all note that this is for MySQL. I'll add an example in case my goal is not clear. The MySQL docs show a method to get almost there:
CREATE TABLE animals ( grp ENUM('fish','mammal','bird') NOT NULL, id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (grp,id) ) ENGINE=MyISAM; INSERT INTO animals (grp,name) VALUES ('mammal','dog'),('mammal','cat'), ('bird','penguin'),('fish','lax'),('mammal','whale'), ('bird','ostrich'); 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 | +--------+----+---------+
I would essentially like to
GROUP BY id, except I would want the records with
mammal to have one "group" for IDs 1-10, another "group" for IDs 11-20, etc. However, I would be doing this with an existing table, and I wouldn't necessarily want "dog" to show up with ID 1. I would want that initial ordering to be random, but then deterministic from then out.