Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: Help with a query please!

    Hi all,

    I have a problem. I have a query which goes and gets a selection of records from a table and groups them by account number. So there may be many recs for each account number. I then want to only return the first 10 records for each account number. Any ideas how I can do this?

    Many thanks,
    Paula.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use analytic functions:

    Code:
    select ...
    from
    ( select ...,
             row_number() over (partition by account_no order by ...) as rn
      from ...
    )
    where rn <= 10;

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thanks, I'll give this a go.

  4. #4
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    I just realised I really don't know what to do with this. Here is the query. Would you mind giving me an exampleof how I use your suggestion?

    Code:
    SELECT
    
      REVENUE_ACCOUNTS.RAC_PAY_REF,
    
      initcap(TRANSACTION_TYPES.TRT_NAME),
    
      TRANSACTION_SUBTYPES.TRS_NAME,
    
      PAYMENT_METHOD_TYPES_TRAN.PMY_DESCRIPTION,
    
      TRANSACTIONS.TRA_EFFECTIVE_DATE,
    
      TRANSACTIONS.TRA_CR,
    
      TRANSACTIONS.TRA_DR
    
    FROM
    
      TRANSACTION_TYPES,
    
      PAYMENT_METHOD_TYPES  PAYMENT_METHOD_TYPES_TRAN,
    
      TRANSACTION_SUBTYPES,
    
      REVENUE_ACCOUNTS,
    
      TRANSACTIONS
    
    WHERE
    
      ( TRANSACTIONS.TRA_TRS_CODE=TRANSACTION_SUBTYPES.TRS_CODE (+)  and TRANSACTIONS.TRA_TRT_CODE=TRANSACTION_SUBTYPES.TRS_TRT_CODE (+)  )
    
      AND  ( TRANSACTIONS.TRA_TRT_CODE=TRANSACTION_TYPES.TRT_CODE  )
    
      AND  ( REVENUE_ACCOUNTS.RAC_ACCNO= TRANSACTIONS.TRA_RAC_ACCNO  )
    
      AND  ( REVENUE_ACCOUNTS.RAC_HRV_ATE_CODE NOT IN ('SUS','SER','MWO')  )
    
      AND  ( PAYMENT_METHOD_TYPES_TRAN.PMY_CODE=TRANSACTIONS.TRA_PMY_CODE  )
    
      AND  (
    
      REVENUE_ACCOUNTS.RAC_PAY_REF  =  '311249G'
    
      AND  TRANSACTION_TYPES.TRT_CODE  =  'PAY'
    
      )
    
    ORDER BY
    
      5   DESC

  5. #5
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    How's this?

    Code:
    select * from (
    
    SELECT
    
      row_number() over (partition by REVENUE_ACCOUNTS.RAC_PAY_REF) as rn,
    
      initcap(TRANSACTION_TYPES.TRT_NAME),
    
      TRANSACTION_SUBTYPES.TRS_NAME,
    
      PAYMENT_METHOD_TYPES_TRAN.PMY_DESCRIPTION,
    
      TRANSACTIONS.TRA_EFFECTIVE_DATE,
    
      TRANSACTIONS.TRA_CR,
    
      TRANSACTIONS.TRA_DR
    
    FROM
    
      TRANSACTION_TYPES,
    
      PAYMENT_METHOD_TYPES  PAYMENT_METHOD_TYPES_TRAN,
    
      TRANSACTION_SUBTYPES,
    
      REVENUE_ACCOUNTS,
    
      TRANSACTIONS
    
    WHERE
    
      ( TRANSACTIONS.TRA_TRS_CODE=TRANSACTION_SUBTYPES.TRS_CODE (+)  and TRANSACTIONS.TRA_TRT_CODE=TRANSACTION_SUBTYPES.TRS_TRT_CODE (+)  )
    
      AND  ( TRANSACTIONS.TRA_TRT_CODE=TRANSACTION_TYPES.TRT_CODE  )
    
      AND  ( REVENUE_ACCOUNTS.RAC_ACCNO= TRANSACTIONS.TRA_RAC_ACCNO  )
    
      AND  ( REVENUE_ACCOUNTS.RAC_HRV_ATE_CODE NOT IN ('SUS','SER','MWO')  )
    
      AND  ( PAYMENT_METHOD_TYPES_TRAN.PMY_CODE=TRANSACTIONS.TRA_PMY_CODE  )
    
      AND  ( TRANSACTION_TYPES.TRT_CODE  =  'PAY')
    
    ORDER BY
    
      5   DESC)
    
    where rn<=10;

  6. #6
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Finished Code which seems to work, for anyone interested.....

    Code:
    select * from (
    SELECT  revenue_accounts.rac_pay_ref, row_number() over (partition by REVENUE_ACCOUNTS.RAC_PAY_REF order by transactions.tra_effective_date desc) as rn,
      initcap(TRANSACTION_TYPES.TRT_NAME),
      TRANSACTION_SUBTYPES.TRS_NAME,
      PAYMENT_METHOD_TYPES_TRAN.PMY_DESCRIPTION,
      TRANSACTIONS.TRA_EFFECTIVE_DATE,
      TRANSACTIONS.TRA_CR,
      TRANSACTIONS.TRA_DR
    FROM
      TRANSACTION_TYPES,
      PAYMENT_METHOD_TYPES  PAYMENT_METHOD_TYPES_TRAN,
      TRANSACTION_SUBTYPES,
      REVENUE_ACCOUNTS,
      TRANSACTIONS
    WHERE
      ( TRANSACTIONS.TRA_TRS_CODE=TRANSACTION_SUBTYPES.TRS_CODE (+)  and TRANSACTIONS.TRA_TRT_CODE=TRANSACTION_SUBTYPES.TRS_TRT_CODE (+)  )
      AND  ( TRANSACTIONS.TRA_TRT_CODE=TRANSACTION_TYPES.TRT_CODE)
      AND  ( REVENUE_ACCOUNTS.RAC_ACCNO= TRANSACTIONS.TRA_RAC_ACCNO)
      AND  ( REVENUE_ACCOUNTS.RAC_HRV_ATE_CODE NOT IN ('SUS','SER','MWO'))
      AND  ( PAYMENT_METHOD_TYPES_TRAN.PMY_CODE=TRANSACTIONS.TRA_PMY_CODE)
      AND  ( TRANSACTION_TYPES.TRT_CODE  =  'PAY'))
    where rn<=10;

Posting Permissions

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