Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: Top 3 from all customers

    Hi,

    I would like to have the last 3 invoices of all customers.
    With my SQL below I can get it from 1 customer (input id = 27 in WHERE clause).

    But how do I do it if I want a complete list:
    - with all customers
    - and from each customer his last 3 invoices ?

    If I take my WHERE-clause away, he just give 3 top from all but that is not what I'm meaning to have.

    Code:
    SELECT top 3 pr.PROJECTNUMBER,
    fctgroep = (SELECT DDDW_VALUE FROM SYNETON_DYNAMIC_DDDW_VALUES WHERE DDDW_VALUE_ID = pr.invoice_group_id),
    inv.INVOICE_DATE, inv.INV_AMOUNT
    FROM INVOICE inv
    INNER JOIN PROJECT pr ON inv.PROJECT_ID = pr.PROJECTID
    /*WHERE (pr.invoice_group_id = 27)*/
    ORDER BY inv.INVOICE_DATE DESC


    Who can help ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Untested "air code", but try:
    Code:
    SELECT PROJECTNUMBER, fotgroep, INVOICE_DATE, INV_AMOUNT
       FROM (SELECT
          pr.PROJECTNUMBER,
          fctgroep = (SELECT DDDW_VALUE
             FROM SYNETON_DYNAMIC_DDDW_VALUES 
             WHERE DDDW_VALUE_ID = pr.invoice_group_id)
    ,     inv.INVOICE_DATE, inv.INV_AMOUNT
    ,     ROW_NUMBER OVER (
             PARTITION pr.invoice_group_id
             ORDER BY ORDER BY inv.INVOICE_DATE DESC) AS rn
          FROM INVOICE inv
          INNER JOIN PROJECT pr ON inv.PROJECT_ID = pr.PROJECTID
          ) AS z
       WHERE rn <= 3
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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