You could also use SELECT GroupID, SomeValue FROM tablename ORDER BY AVG(CAST(0+CRYPT_GEN_RANDOM(4) AS FLOAT)) OVER (PARTITION BY GroupID), GroupID, --Just in case 2 groups get the same random number SomeValue; Or WITH T AS (SELECT GroupID, SomeValue, CASE WHEN ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY GroupID) = 1 THEN CRYPT_GEN_RANDOM(4) END AS rand FROM tablename) SELECT GroupID, SomeValue FROM T ORDER BY MAX(rand) OVER (PARTITION BY GroupID), GroupID, SomeValue; [1]: http://rextester.com/ZFVOA54019