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

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.

  • I would want that initial ordering to be random, but then deterministic from then out. <-- say what? I think no matter what you do you're going to have to put the records in a second table of some sort. How precisely does this business logic work? As is there's nothing to require (for example) dog to come first. And what do you mean by I would want the records from *mammal* to have one "group" for IDs 1-10, and another for IDs 11-20 ... can you illustrate that with another table, focusing on mammals, in the above question description?
    – jcolebrand
    Commented Apr 2, 2011 at 2:39
  • @jcolebrand For every record that is a mammal I want to assign a unique id from 1 to numMammal. I don't really care what id dog gets, but I do not want it to depend upon the original insertion order. Commented Apr 2, 2011 at 3:01
  • @jcolebrand Suppose I also had a weight column. I might want to take the average weight of mammals with IDs from 1-10 and the average weight of mammals with IDs from 11-20, etc. That is the sense that I want to GROUP BY. I might then want to pair groups of 10 to find the correlation between the average. I need this random ordering because if the original insertion order happened to be sorted by weight then this would give me the wrong results. I hope I'm making sense. Commented Apr 2, 2011 at 3:05
  • I still think a sample TABLE in the question would be helpful. But I think I see what you want. I just don't see where those things are the domain of SQL, since it's not really about sets. SQL is the domain of sets. I would do the logic you're suggesting in a php file with a single (or two) loops. SQL would be doing an effective single loop to assign the numbers anyways.
    – jcolebrand
    Commented Apr 2, 2011 at 6:06
  • @jcolebrand It may well be that I shouldn't be doing this in SQL, but I thought a useful rule of thumb was to let the database do the work for you. I'm still learning the bounds of what should and should not be processed within the database, but in the past when I've tried pulling out results, processing them, and then sticking the results back in, I've gotten poor performance results (hours and hours because I was probably doing something wrong in inserting the results back in). Commented Apr 2, 2011 at 19:37

4 Answers 4


What about doing a little math against your ID column to dynamically generate the group?

SELECT grp, FLOOR(id/10) AS id_grp
FROM animals
GROUP BY grp, id_grp

This would give you groups of 10 based on the ID of the record. I used your animals table above to generate the data below.

Sample data


Query Output

 | grp    | id_grp |
 | fish   |      4 |
 | fish   |      5 |
 | mammal |      1 |
 | mammal |      2 |
 | mammal |      3 |
 | bird   |      1 |
 | bird   |      3 |
 7 rows in set (0.00 sec)

In SQL generally this would be :

  • a DISTINCT subselect
  • JOIN back to main table on DISTINCT keys
  • NTILE with PARTITION BY on DISTINCT keys and an ORDER BY to create buckets

It isn't an aggregate so GROUP BY isn't needed


Actually, NTILE is enough by itself to create "n buckets per set of distinct values"

  • I don't believe MySQL supports NTILE. Commented Mar 28, 2011 at 6:15
  • Sorry, that link implies it does. There is probably a solution/workaround for NTILE out there.
    – gbn
    Commented Mar 28, 2011 at 6:47
  • Great Oracle solution. Commented Apr 1, 2011 at 13:11
  • @Leigh Riffel: and SQL Server. And Sybase. And PostGres...
    – gbn
    Commented Apr 1, 2011 at 13:18
  • 2
    @gbn Not MySQL was the point I should have made clear. The article references Oracle. Commented Apr 1, 2011 at 13:20

I'm still not seeing any complete solutions (that actually work in MySQL), so this is the solution I will probably use:

  1. Generate the random IDs outside of SQL entirely (in some sort of script)
  2. Apply integer division on those IDs to group those accordingly.

I'm still hoping somebody can beat this answer; I don't want to have to accept my own answer. I've said this before, but I knew from the beginning how to do #2; #1 is what's been troubling me. If you can answer #1 then you actually answer another question as well, but it might be possible to answer this question in some other way so as to bypass #1.

-- Change 'ValueField' to whatever provides your 'group' values

set @rownum := 0;
set @groupnum := 0;
set @lastGroup := 0;

    count(1) as Count
        -- We have a row number for each record
            -- Set the record number
            case when @lastGroup != ValueField 
                then @rownum := 0 else (@rownum := @rownum + 1) 
            end as Record, 

            -- Determine which group we are in
                -- If the 'Group' changed, reset our grouping
                when @lastGroup != ValueField 
                    then @groupnum := 0

                -- Determines the grouping value; group size is set to 10
                when floor(@rownum / 10) != @groupnum 
                    then @groupnum := @groupnum + 1 
                else @groupnum
            end as Grouping,

            -- Track the last Group
                when @lastGroup != ValueField 
                    then @lastGroup := ValueField 
                else @lastGroup 
            end as LastGroup,

            -- Value field that will be aggregated
        order by 
    ) as x
group by

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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