I want to write a query showing customer account numbers and account types.

Is there any way to pick say only 10 customer account numbers for each account type?

I just want a sample of data to be shown.

Thanks,
Beth

Hi there.

What database are you working on ?

CVM.

Hello there,

Oracle Version 8

thanks,
Beth

Hi Beth,

You can query as,

Select cust_name, acct_type
From customer
Where ROWNUM <= 10;

That will give you first 10 rows of the customer table.

Thanks,
Rohit.

Thanks for this,
Is there any way to show the top 10 for each account type?

Beth

Try this.

Select cust_name, acct_type
From customer t1
Where t1.cust_name in
(select t2.cust_name from customer t2 where t2.acct_type = t1.acct_type and rownum <=) 10

Hi Beth,

Try the following logic,

CURSOR acct IS
SELECT DISTINCT acct_type FROM customer;

BEGIN
FOR i IN acct LOOP
SELECT acct_no
BULK COLLECT INTO l_acct_no
FROM customer
WHERE ROWNUM <=10;
END LOOP;
END;

Thanks,
Rohit.

Thanks for this,
Is there any way to show the top 10 for each account type?

Beth

the top 10 customers in each account based on what field? customer name?

rudy

Thank you for these replies

I have been using this sql but unfortunately it is brining back a lot less rows than I expect.

I now want to show 10 receipt transactions for each account type.

I am using the following but it is only selecting 4 rows, I now for a fact there were 2900 cheque transactions going to 13 different account types during September.

select tran_account_no,
tran_amount_cr,
tran_date,
tran_pay_method,
tran_act_code
from transactions,accounts
where tran_account_no = acc_account_no
and tran_gtrans_type = 'R'
and tran_pay_method = 'Q'
and tran_date >= '01-SEP-2003'
and tran_date <= '30-SEP-2003'
and tran_account_no in (select acc_account_no from accounts where tran_act_code = acc_act_code and nownum <=10)
order by tran_act_code
;

The information is needed for system testing, I want a random-is selection of data.

Thanks everyone
Beth

Dear beth,

I am suresh, i want to know which version of oracle r u using. If it is
oracle 8i then try the following query for select 10 customer account number for each account type

select * from (select customer_acc_no, row_number() over (partition by account_type order by account_type) rn
from table)
where rn <= 10

Thanks