0

I have a simple results set that gives me a User ID and an Operator from a table that contains other columns, one of which is a GUID based on an activity that has taken place for that user. I've added a row number and partitioned by the Operator to get something like this (but much more expansive than this example);

UserId    Operator        RowNumber
513       J. Smith        12
513       J. Smith        56
513       J. Smith        25
513       J. Smith        45
513       J. Smith        11
513       J. Smith        58
513       J. Smith        22
513       J. Smith        17
513       J. Smith        29
501       S. Jones        33
501       S. Jones        21
501       S. Jones        09
501       S. Jones        15
501       S. Jones        39
501       S. Jones        26
501       S. Jones        31
501       S. Jones        28
501       S. Jones        37

Imagine double the number of rows per operator and about 20 distinct operator names.

I want to be able to return 6 randomised rows per operator in the same results set. I've got as far as getting randomised rows for one operator at a time by enclosing my original query in a sub-query and selecting the TOP 6 from that with a row number and partition and used ORDER BY newid() at the other end. I just can't figure out how to get 6 random rows (based on the row number I've given it) per user all at once.

Phteven
  • 139
  • 1
  • 13
  • Welcome to SO. We are not here to write your code. Please add a [mcve] detailing your code. Explain why it does not work for you and what you expect it to do. – Adriaan Sep 14 '15 at 10:13
  • Apologies, duly noted for any future questions. For the sake of completeness I will share below what I ended up getting to work, however the offered solution is in fact much easier to effect and more efficient than what I cobbled together before it showed up. – Phteven Sep 15 '15 at 09:22
  • I give up. I've tried to add my version to the solution and as an edit to the question and just keep getting the message "An error has occurred trying to post the edit/solution" depending on which way I try to post it. Great that you don't get any detail telling you what you've done wrong in the textbox. If that's even the problem! – Phteven Sep 15 '15 at 09:32

1 Answers1

2

The trick is to use the order by clause in the ROW_NUMBER declaration:

   ;WITH CTE AS (
    SELECT USERID, OPERATOR
    , ROW_NUMBER() OVER(
        PARTITION BY OPERATOR 
            ORDER BY NEWID()) AS RN
    FROM [TABLE])
    SELECT *
    FROM CTE
    WHERE RN <= 6
John Bell
  • 2,350
  • 1
  • 14
  • 23
  • Thank you John. I managed to get a dirty version to work before finding your response and switched it to your version in the end, it's so much easier to implement. I tried to add the code I got working as a solution below but I keep getting an error so can't post it. – Phteven Sep 15 '15 at 09:29