How to order rows randomly but keep groups together?

Question

My table looks like this:

GroupID INT NOT NULL
SomeValue INT NOT NULL

I'd like to keep rows with the same GroupID together in the result set. The groups themselves should be ordered randomly, though. SomeValue is supposed to be the secondary sort criterion. Like this:

GroupID, SomeValue
13, 1
13, 2
7, 1
7, 2
11, 1
11, 2
...

ORDER BY GroupID, SomeValue does not order groups randomly. ORDERBY NEWID() is totally random. ORDER BY SomeHash(GroupID), SomeValue comes to mind but I need a new random order each time.

How can this be done?

Answer 1

You only need a random order for the GroupID values, so first get these distinct values and a random number for each, in a derived table:

SELECT t.*
FROM tablename AS t
  JOIN
    ( SELECT GroupID, 
             NEWID() AS rnd   -- or another random function
      FROM tablename
      GROUP BY GroupID
    ) AS r
    ON r.GroupID = t.GroupID 
ORDER BY 
    r.rnd, 
    t.SomeValue ;

If you already have a Groups table which holds the GroupID values, you can replace that FROM tablename GROUP BY GroupID with FROM Groups.

Answer 2

You could also use (Thanks to ypercubeᵀᴹ for the fix)

SELECT GroupID,
       SomeValue
FROM   tablename
ORDER  BY FIRST_VALUE(CRYPT_GEN_RANDOM(4)) OVER (PARTITION BY GroupID ORDER BY GroupID),
          GroupID, --Just in case 2 groups get the same random number
          SomeValue;

Online demo