Randomise while keeping ascending inside the group

Question

I have three main tables as follows:

SELECT
    Q.QPackID, Q.QGrpID, Q.QID
FROM
    dbo.SV_Q AS Q 
LEFT OUTER JOIN
    dbo.SV_Q_Pack AS P ON Q.QPackID = P.QPackID 
LEFT OUTER JOIN
    dbo.SV_QGrp AS G ON Q.QGrpID = G.QGrpID 
LEFT OUTER JOIN
    dbo.SV_QM AS M ON Q.QmID = M.QmID
WHERE
    Q.QID IN (SELECT QID FROM MyTable WHERE MyID = 123); 

QPackID	QGrpID	QID
1	1	1
1	2	2
1	2	3
2	3	4
2	3	5
2	4	5
3	5	6
3	5	7
3	5	8
3	6	9
4	7	10
4	8	11
4	8	12

• QID belongs to QGrpID
• QGrpID belongs to QPackID

I want to sort this based on:

• QPackID to be randomised while keeping fixed within the same QPackID
• QGrp to be randomised while keeping fixed within the same QGrpID
• QID to be kept ascending under QGrpID

An expected result is like this:

QPackID	QGrpID	QID
4	8	11
4	8	12
4	7	10
2	3	4
2	3	5
2	4	5
1	2	2
1	2	3
1	1	1
3	6	9
3	5	6
3	5	7
3	5	8

Could anyone help me with this?

Answer 1

I believe this will give you the outcome you want without having to join the table to itself multiple times -

DECLARE @X DECIMAL(5, 5);

SELECT @X = RAND();

SELECT Q.QPackID
     , Q.QGrpID
     , Q.QID
FROM dbo.SV_Q AS Q
    LEFT JOIN dbo.SV_Q_Pack AS P
        ON Q.QPackID = P.QPackID
    LEFT JOIN dbo.SV_QGrp AS G
        ON Q.QGrpID = G.QGrpID
    LEFT JOIN dbo.SV_QM AS M
        ON Q.QmID = M.QmID
WHERE Q.QID IN
      (
          SELECT QID FROM MyTable WHERE MyID = 123
      )
ORDER BY (CAST(Q.QPackID AS DECIMAL(23)) % @X) DESC
       , (CAST(Q.QGrpID AS DECIMAL(23)) % @X) DESC
       , QID ASC;

For your query, adjust the DECIMAL(n) in the ORDER BY clause to your desired size, i.e. if QPackID is a column containing a maximum of 9 digits, use DECIMAL(9).

Example below - DB Fiddle