Post reply

How to create a stored procedure where the top-3 is randomly ordered each time

  • May 29, 2018 at 2:30 am

    #349436

    Hi there,

    I'm not able to create the right stored procedure for this:

    I have a table ORDER BY myPrio ASC. Now I need to display the records in that order, but the first 3 must be shuffled every time.
    Example:
    1st result: 1 2 3 4 5 6 7 8 9 
    2nd result: 3 1 2 4 5 6 7 8 9
    3rd result: 3 2 1 4 5 6 7 8 9
    4rd result: 2 1 3 4 5 6 7 8 9
    etc...

    Should I set myPrio to 1 for the first 3 and then randomize? Or shuffle the output after I retrieve the recordset?
    Anybody?

  • May 29, 2018 at 2:52 am

    #1992141

    What you're looking for might be something like:

    ORDER BY CASE WHEN i <= 3 THEN 0 ELSE 1 END, CASE WHEN i <= 3 THEN CHECKSUM(NEWID()) ELSE 0 END

    However, your i value might need to be based on a row_number() field, depending on if you have multiple records of the same priority level...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • May 29, 2018 at 4:28 am

    #1992146

    Thanks Thomas, I will see if I can build something with that. What is the Checksum part?

    Regards,

    Ruud

  • May 29, 2018 at 4:38 am

    #1992148

    CHECKSUM(NEWID()) is a way of generating a random number.  There are...issues with the T-SQL RAND function that can be illustrated with the following:

    DECLARE @t TABLE (i INT)
    INSERT INTO @t VALUES (1), (2), (3), (4), (5), (6);
    SELECT i, RAND(), NEWID() FROM @t

    As you will see, the RAND() function generates one random number per result set; the NEWID function generates one per row.  The CHECKSUM() part is probably optional for this requirement, but can be used to generate random numbers in a certain range - change the last line in the query above as shown below, and look at the results for multiple runs:

    DECLARE @t TABLE (i INT)
    INSERT INTO @t VALUES (1), (2), (3), (4), (5), (6);
    SELECT i, RAND(), ABS(CHECKSUM(NEWID())%3) FROM @t

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • May 29, 2018 at 8:51 am

    #1992176

    Works like a charm! Thanks!

    Ruud

  • May 31, 2018 at 9:12 am

    #1992494

    Works like charm, that's what I said, but there are problems:

    When the randomizer generates the same number every time (which is statistically very possible) for the records, the priority order could become even 1-1-1-1-1-1 in the example. This creates some problems.

    Because of that, the question becomes more like: I've got 5 records A,B,C,D,E of which the priority is shuffled every time using the distinct numbers 1,2,3,4,5.

    A solution like adding the same random number does not work because it doesn't change the actual sequence.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply