Skip to main content
Post Undeleted by Martin Smith
added 57 characters in body
Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

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

SELECT GroupID,
       SomeValue
FROM   tablename
ORDER  BY AVG(CASTFIRST_VALUE(0+CRYPT_GEN_RANDOMCRYPT_GEN_RANDOM(4) AS FLOAT)) OVER (PARTITION BY GroupID ORDER BY GroupID),
          GroupID, --Just in case 2 groups get the same random number
          SomeValue; 

OrOnline demo

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; 

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; 

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

Post Deleted by Martin Smith
added 472 characters in body
Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

You could also use

SELECT GroupID,
       SomeValue
FROM   tablename
ORDER  BY MAXAVG(CRYPT_GEN_RANDOMCAST(0+CRYPT_GEN_RANDOM(4) AS FLOAT)) OVER (PARTITION BY GroupID),
          GroupID, --Just in case 2 groups get the same random number
          SomeValue; 

Online demo 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; 

You could also use

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

Online demo

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; 
added 23 characters in body
Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342

You could also use

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

Online demoOnline demo

You could also use

SELECT GroupID,
       SomeValue
FROM   tablename
ORDER  BY MAX(CRYPT_GEN_RANDOM(4)) OVER (PARTITION BY GroupID),
          SomeValue; 

Online demo

You could also use

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

Online demo

Source Link
Martin Smith
  • 86.5k
  • 15
  • 252
  • 342
Loading