sql-serversql-server-2019

Randomise while keeping ascending inside the group


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?


Solution

  • 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