I need to select random groups of data I am working on a query that requires the retrieval of 20 random rows from a cash book table, which I am doing using 'Top 20 &amp; order by newId()'.
Let me explain: The cash book table is a list of customers with Account Number, Name, Balance. Each customer, depending on the number of Accounts they have will return a different number of rows. ie: one account will return one row, 7 accounts will return 7 rows etc.
The number of Customers can be anything between 150 - 300 depending on which cash book i'm looking at. So if each Customer had 3 accounts there could potentially be 900 rows in that particular cash book table.
What i need to do is return 20 'groups of results'.
So if customer one has 6 accounts, it returns 6 rows but only classes that as a single return, meaning that it will return the account details for another 19 customers irrespective of the number of accounts they have.
That's probably not very clear so i apologise, but if anyone can help i would be very grateful. regards, rich
I'm not entirely clear on whether I understand you problem or not, but  assuming there is a customer_id attribute you can partition by that:

select ... from (
    select ..., row_number() over (partition by customer_id) as rn
    from ...
)
where rn = 1


This will give you one row per customer, in an undefined order. If you want a true random account per customer you can add order by some random function after partition by:

    select ..., row_number() over (partition by customer_id
                                   order by ...) as rn