3

I have a stored procedure that produces data for a call center system every morning. However, the data is sent in the same order daily. Is there a way to send the day in a different order every day without having to manually change the where clause? For examples today's data is : 1,2,3 tomorrow's will be :3,2,1 the day after 2,1,3 .. Same data different order daily.

6
  • Can you show how you change the WHERE clause manually to get a different order? Did you mean the ORDER BY clause? Seeing some code and a rough description of the table structure, data, and how you want the data ordered would be useful (I don't know what :3,2,1 means). – Aaron Bertrand Aug 27 '15 at 14:14
  • Also, do you want the order to be random, or do you want the order to follow a specific pattern? If random, then do you need to keep track of the previous day's order to ensure you don't repeat it? "Scramble" and "change the order in this specific way" are two very different things. – Aaron Bertrand Aug 27 '15 at 14:15
  • Shouldn't ordering of the data be handled at the application layer? – datagod Aug 27 '15 at 14:18
  • 3
    @datagod I know Brent wants people to believe that order should never be dictated inside SQL Server, but I don't know that many people agree with him. The details are scant, but the question doesn't seem to indicate that order should really change, just which row appears first. – Aaron Bertrand Aug 27 '15 at 14:20
  • I order inside SQL when I need to, I am not against it. I do this when the "end app" is a report, or a batch process that I created. I worry though that applications taking the data will re-sort the data anyway. – datagod Aug 27 '15 at 15:01
5

Assuming you want randomish ordering of your output you can just ORDER BY NEWID(). That would jumble up the ordering of the results reasonably well. If you only need to randomize after a certain order is established then you can still use the NEWID() trick. Below are some quick examples:

-- Fully random ordering
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    NEWID() ;

-- Random ordering of results but the results still preserve the order of UserId
SELECT
    *
FROM dbo.Foo F
    INNER JOIN dbo.Bar B ON B.Id = F.FkId
WHERE
    B.ImportantFilter > @ImpressiveVariable
ORDER BY
    F.UserId
    , NEWID() ;

Another way, which will not produce all possible orderings but only a limited set but can be more efficient with large tables because it doesn't use NEWID(), only a single value for @aRandomNumber which should be drawn from the range of the column that is compared against (F.UserId here) :

WITH cte AS
  ( SELECT *, 0 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId >= @aRandomNumber
    ORDER BY F.UserId
  UNION ALL
    SELECT *, 1 AS ord
    FROM dbo.Foo F
        INNER JOIN dbo.Bar B ON B.Id = F.FkId
    WHERE ( <where conditions> )
      AND F.UserId < @aRandomNumber
    ORDER BY F.UserId
  )
SELECT <list of columns except the "ord">
FROM cte
ORDER BY ord, UserId ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.