Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Question Unanswered: selecting last 3 dates

    Hi all

    I have an invoice table that I would like to query for customers and there last 3 inoivce amounts. In the table there are the following fields: customer number, invoice date, invoice amount, previous invoice date, and previous invoice amount. I am totally stumped on how I can query on the last 3 invoices for a particular customer. Can someone show me how I can overcome this hurdle.

    Thanks

  2. #2
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    Code:
    SQL> desc xyz
     Name                          Null?    Type
     ----------------------------- -------- --------------------
     CUST_ID                                NUMBER(10)
     INVOICE_DT                             DATE
     INVOICE_AMT                            NUMBER(10,2)
     PREVIOUS_INVOICE_DT                    DATE
    
    SQL> select * from xyz order by invoice_dt, cust_id
      2  /
    
       CUST_ID INVOICE_D INVOICE_AMT PREVIOUS_
    ---------- --------- ----------- ---------
             1 21-AUG-02          32 21-JUL-02
             2 21-AUG-02          64 21-JUL-02
             3 21-AUG-02         102 21-JUL-02
             4 21-AUG-02         146 21-JUL-02
             5 21-AUG-02         196 21-JUL-02
             6 21-AUG-02         252 21-JUL-02
             1 21-SEP-02          26 21-AUG-02
             2 21-SEP-02          57 21-AUG-02
             3 21-SEP-02          94 21-AUG-02
             4 21-SEP-02         137 21-AUG-02
             5 21-SEP-02         186 21-AUG-02
             6 21-SEP-02         241 21-AUG-02
             1 21-OCT-02          21 21-SEP-02
             2 21-OCT-02          51 21-SEP-02
             3 21-OCT-02          87 21-SEP-02
             4 21-OCT-02         129 21-SEP-02
             5 21-OCT-02         177 21-SEP-02
             6 21-OCT-02         231 21-SEP-02
             1 21-NOV-02          17 21-OCT-02
             2 21-NOV-02          46 21-OCT-02
             3 21-NOV-02          81 21-OCT-02
             4 21-NOV-02         122 21-OCT-02
             5 21-NOV-02         169 21-OCT-02
             6 21-NOV-02         222 21-OCT-02
             1 21-DEC-02          14 21-NOV-02
             2 21-DEC-02          42 21-NOV-02
             3 21-DEC-02          76 21-NOV-02
             4 21-DEC-02         116 21-NOV-02
             5 21-DEC-02         162 21-NOV-02
             6 21-DEC-02         214 21-NOV-02
             1 21-JAN-03          12 21-DEC-02
             2 21-JAN-03          39 21-DEC-02
             3 21-JAN-03          72 21-DEC-02
             4 21-JAN-03         111 21-DEC-02
             5 21-JAN-03         156 21-DEC-02
             6 21-JAN-03         207 21-DEC-02
    
    36 rows selected.
    
    SQL> l
      1  select * from (
      2     select
      3        cust_id,
      4        invoice_dt,
      5        invoice_amt,
      6        previous_invoice_dt,
      7        rank () over (partition by cust_id order by invoice_dt desc) as rk
      8     from xyz ) i
      9* where i.rk < 3
    SQL> /
    
       CUST_ID INVOICE_D INVOICE_AMT PREVIOUS_         RK
    ---------- --------- ----------- --------- ----------
             1 21-JAN-03          12 21-DEC-02          1
             1 21-DEC-02          14 21-NOV-02          2
             2 21-JAN-03          39 21-DEC-02          1
             2 21-DEC-02          42 21-NOV-02          2
             3 21-JAN-03          72 21-DEC-02          1
             3 21-DEC-02          76 21-NOV-02          2
             4 21-JAN-03         111 21-DEC-02          1
             4 21-DEC-02         116 21-NOV-02          2
             5 21-JAN-03         156 21-DEC-02          1
             5 21-DEC-02         162 21-NOV-02          2
             6 21-JAN-03         207 21-DEC-02          1
             6 21-DEC-02         214 21-NOV-02          2
    
    12 rows selected.

  3. #3
    Join Date
    Oct 2002
    Location
    greenwich.ct.us
    Posts
    279
    of course, I meant
    Code:
    SQL> l
      1  select * from (
      2     select
      3        cust_id,
      4        invoice_dt,
      5        invoice_amt,
      6        previous_invoice_dt,
      7        rank () over (partition by cust_id order by invoice_dt desc) as rk
      8     from xyz ) i
      9* where i.rk <= 3
    SQL> /
    
       CUST_ID INVOICE_D INVOICE_AMT PREVIOUS_         RK
    ---------- --------- ----------- --------- ----------
             1 21-JAN-03          12 21-DEC-02          1
             1 21-DEC-02          14 21-NOV-02          2
             1 21-NOV-02          17 21-OCT-02          3
             2 21-JAN-03          39 21-DEC-02          1
             2 21-DEC-02          42 21-NOV-02          2
             2 21-NOV-02          46 21-OCT-02          3
             3 21-JAN-03          72 21-DEC-02          1
             3 21-DEC-02          76 21-NOV-02          2
             3 21-NOV-02          81 21-OCT-02          3
             4 21-JAN-03         111 21-DEC-02          1
             4 21-DEC-02         116 21-NOV-02          2
             4 21-NOV-02         122 21-OCT-02          3
             5 21-JAN-03         156 21-DEC-02          1
             5 21-DEC-02         162 21-NOV-02          2
             5 21-NOV-02         169 21-OCT-02          3
             6 21-JAN-03         207 21-DEC-02          1
             6 21-DEC-02         214 21-NOV-02          2
             6 21-NOV-02         222 21-OCT-02          3
    
    18 rows selected.

  4. #4
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Thumbs up

    This is perfect. Thanks for your help.


    Originally posted by marist89
    of course, I meant
    Code:
    SQL> l
      1  select * from (
      2     select
      3        cust_id,
      4        invoice_dt,
      5        invoice_amt,
      6        previous_invoice_dt,
      7        rank () over (partition by cust_id order by invoice_dt desc) as rk
      8     from xyz ) i
      9* where i.rk <= 3
    SQL> /
    
       CUST_ID INVOICE_D INVOICE_AMT PREVIOUS_         RK
    ---------- --------- ----------- --------- ----------
             1 21-JAN-03          12 21-DEC-02          1
             1 21-DEC-02          14 21-NOV-02          2
             1 21-NOV-02          17 21-OCT-02          3
             2 21-JAN-03          39 21-DEC-02          1
             2 21-DEC-02          42 21-NOV-02          2
             2 21-NOV-02          46 21-OCT-02          3
             3 21-JAN-03          72 21-DEC-02          1
             3 21-DEC-02          76 21-NOV-02          2
             3 21-NOV-02          81 21-OCT-02          3
             4 21-JAN-03         111 21-DEC-02          1
             4 21-DEC-02         116 21-NOV-02          2
             4 21-NOV-02         122 21-OCT-02          3
             5 21-JAN-03         156 21-DEC-02          1
             5 21-DEC-02         162 21-NOV-02          2
             5 21-NOV-02         169 21-OCT-02          3
             6 21-JAN-03         207 21-DEC-02          1
             6 21-DEC-02         214 21-NOV-02          2
             6 21-NOV-02         222 21-OCT-02          3
    
    18 rows selected.

Posting Permissions

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