Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Unanswered: selecting only 10 of each

    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
    Beth

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Hi there.

    What database are you working on ?

    CVM.

  3. #3
    Join Date
    Jul 2003
    Location
    England
    Posts
    26
    Originally posted by cvandemaele
    Hi there.

    What database are you working on ?

    CVM.
    Hello there,

    Oracle Version 8

    thanks,
    Beth
    Beth

  4. #4
    Join Date
    Oct 2003
    Posts
    12

    Smile Re: selecting only 10 of each

    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.

    Originally posted by elisabeth
    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

  5. #5
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Re: selecting only 10 of each

    Originally posted by krohit
    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
    Beth

  6. #6
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    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

  7. #7
    Join Date
    Oct 2003
    Posts
    12

    Smile Re: selecting only 10 of each

    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.

    Originally posted by elisabeth
    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

  8. #8
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Re: selecting only 10 of each

    Originally posted by krohit
    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
    Beth

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the top 10 customers in each account based on what field? customer name?

    rudy

  10. #10
    Join Date
    Jul 2003
    Location
    England
    Posts
    26
    Originally posted by cvandemaele
    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
    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
    Beth

  11. #11
    Join Date
    Oct 2003
    Location
    Chennai, India
    Posts
    2

    Re: selecting only 10 of each

    Originally posted by elisabeth
    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
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •