SQL Server 2014 – How to Order Rows Randomly but Keep Groups Together

sql serversql server 2014

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?

Best Answer

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.

Related Question