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 & 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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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